Re: SUMIFS and WEEKNUM
Quote from NBVC;670204=SUMPRODUCT(--(WEEKNUM(Sheet1!$E$2:$E$100)=A8),Sheet1!$H$2:$H$100)
I don't believe that formula will work (certainly not in Excel 2007) because WEEKNUM function can't handle ranges - in Excel 2007 and later, though, it can handle arrays so you can use +0 to convert like this:
=SUMPRODUCT(--(WEEKNUM(Sheet1!$E$2:$E$100+0)=A8),Sheet1!$H$2:$H$100)
Re: How Reduce Formula
I assume this is a Brazilian version of Excel, or Portuguese so SE = IF and PROC = LOOKUP
That doesn't really explain the formula, though, because it's a series of IF functions like
=IF($A20=TOROS!$A$177;LOOKUP($A20;TOROS!$A$177;TOROS!A$232)
That uses LOOKUP with a lookup range of a single cell (!) so can be reduced to this
=SE($A20=TOROS!$A$177;TOROS!A$232)
and you can do the same throughout the formula.
You could reduce considerably if there was some pattern to the arrangements....but there appear to be slight variations, e.g. the above tests A20 against A177 and if equal returns the value from 55 cells further down the column......but later in the formula this changes to 56...and the cells being tested are also variable distances apart. If you make the pattern consistent then you could use a simple one line formula........
Re: countif criteria issue
Don't know about the macro part but in COUNTIF you could simply add those two values as the criteria, e.g. if your data range is column A - your date (for the criteria) is in B2 and time in C2 you can use
=COUNTIF(A:A,"<"&B2+C2)
Re: Last Working Day of Month
Quote from dtully;589640=WORKDAY(EOMONTH($D$1,0),IF(WEEKDAY(EOMONTH($D$1,0),2)>5,1,0))
Is that supposed to give last workday in the month? Surely it doesn't, what if D1 is today?
This thread is more than 6 years old so I'm not sure whether Cameron is still interested but this is simplest if you use WORKDAY to go back 1 working day from the 1st of the next month, i.e.
=WORKDAY(EOMONTH($D$1,0)+1,-1)
Re: Finding max value in an array that includes #N/A using VLOOKUP
You have Excel 2010, right?
Try using AGGREGATE function, i.e.
=AGGREGATE(4,6,D7:D29)
[4 indicates MAX, 6 indicates "ignore errors"]
Re: Vlookup / networkdays required
I don't get #N/A when I use Kris' formula on your sample...but there are some strange results because you have rows where the date column is blank - what should happen then - what result do you expect. Also what about the situations when there are 2 or more matches, which date would you want. Is it the latest date?
Re: Converting Date DD-MMM-YYYY to DD/MM/YYYY
In your example the month name is in English (May not Mai), is that true for all dates? What does 1st May look like, is it 01-MAY-2009 with a leading zero or 1-MAY-2009?
Re: VLookup Is Not Corresponding To Table
mmmm.....
I don't think sorting is the answer. One problem with that approach is that you can get "false matches" for codes that don't exist, e.g. what result do you get if you put "ZZ" in B20?
For this sort of situation I assume that only an exact match makes sense.......so the 4th argument of VLOOKUP needs to be included, i.e.
=VLOOKUP($B20,Rates,2,FALSE)
With this version the table order is unimportant, it can be unsorted. Now if B20 is "ZZ" the formula returns #N/A
Re: COUNTIF the date in one column is within the last 7 days of a date in another cel
Try using COUNTIFS, i.e.
=COUNTIFS(I7:I106,">"&L3-7,I7:I106,"<="&L3,J7:J106,"Deficient")
Re: Future date by adding number of days including all days except the days I specify
From your profile I see you have excel 2007. For that version you could try this array formula in C1
=A1+SMALL(IF(COUNTIF(Sheet2!A$1:A$100,A1+ROW(INDIRECT("1:"&B1*10))),"",ROW(INDIRECT("1:"&B1*10))),B1)
confirmed with CTRL+SHIFT+ENTER
In Excel 2010 there is an easier formula solution available by using the new WORKDAY.INTL function, i.e.
=WORKDAY.INTL(A1,B1,"0000000",Sheet2!A$1:A$100)
Re: SMALL FUNCTION excluding blank cells
Try this "array formula" in AL92
=SMALL(IF(TEXT(Table1[Date],"dddd")=Z92,IF(Table1[Date]<>"",IF(Table1[[Early AM]:[Bed Time]]>0,Table1[[Early AM]:[Bed Time]]))),1)
confirm with CTRL+SHIFT+ENTER and copy down to AL98 to get the minimum non-zero (and non-blank) value for each day. Note you need to correct the spelling of "WEDNESDAY" in Z95.........
Re: Sumproduct severely slowing down spreadsheet
How many rows are you referencing with the SUMPRODUCT formulas in your real data?
It may be that OFFSET is contributing to the inefficiencies - OFFSET is a "volatile" function which means that it recalculates every time something is changed even if data directly affecting that formula doesn't change - INDEX is better, e.g. in C19 copied down
=SUMPRODUCT($M$5:$M$13,INDEX($G$5:$K$13,0,MATCH($A19,$G$4:$K$4,0)))
and similar for your other formulas
Re: Using countif to count word in specific colums only
No you don't need VBA, try this formula in E3 copied down
=COUNTIFS(F$2:EN$2,"Shift Status",F3:EN3,"Working")
Re: IF function returning 0(blank) instead of TRUE.
Sounds like you are misunderstanding how VLOOKUP works - if you use a formula like this
=VLOOKUP("x",A2:B10,2,0)
Then if "x" is found in A2 the formula returns the value of B2 - if B2 is a blank you don't get #N/A, that only occurs if "x" isn't found anywhere in the lookup range (A2:A10). If you want to return some message if B2 is blank then you can do that like this
=IF(Vlookup_formula="","blank",Vlookup_formula)
You can replace "blank" with any text you want, even "" (a blank)
If you also want "not found" then you could add an IFERROR function to that, i.e.
=IFERROR(IF(Vlookup_formula="","blank",Vlookup_formula),"not found")
Re: Formula to count unique with 3 criteria.
Just a small suggestion.....
rather than "hardcoding" the character length [10] in this part
LEFT(Workorders!$K$2:$K$25000,10)=Individuals!O$27
use LEN function, i.e.
LEFT(Workorders!$K$2:$K$25000,LEN(Individuals!O$27))=Individuals!O$27
Now you can change cell O27 to anything you want without having to change the formula........
Re: Calculate Average of First Lowest Scores over a Timespan
The answer here depends on your setup. Are the scores in a row or a column, are the first scores at the top of the bottom (or left or right). If the player isn't there what goes in the cell for that week, is it left blank or filled with zero, text or something else?
Assuming you have scores in B2:K2 with week 1 score in B2 and blanks where the player isn't there try this
=SUM(SMALL(B2:INDEX(B2:K2,SMALL(IF(B2:K2<>"",COLUMN(B2:K2)-COLUMN(B2)+1),4)),{1,2,3}))/3
That's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER
You'll get an error if there are less than 4 numbers - what should happen then, e.g. if there are only 2 or 3 scores?
Re: Search for value and return text in column header
Assuming your table is in A1:D5 then with a specific zip code in F2 try this "array formula" to get the relevant header
=INDEX(A1:D1,MIN(IF(A2:D5=F2,COLUMN(A2:D5)-COLUMN(A2)+1)))
Formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar
Re: Round up / down date/time format?
Hello russ101,
I see you posted this query at MrExcel too. Crossposting is against Ozgrid rules unless you also include a link to that post - here it is - please see barry's reply!
Re: Using IF(COUNTBLANK) Formula but want to exclude cells filled with N/A
Try flipping the problem......
You want "Y" when the range has 2 dates? COUNT counts dates but not errors
=IF(COUNT('Special Vips'!K2:K20)=2,"Y","N")
Re: SUMIFS formula w/ variable Sum_Range
You can simply match B15 against the headers in E3 and F3 (on Data sheet). No need for any other table listing the ranges. Try this formula
=SUMIFS(INDEX(Data!$G:$H,0,MATCH($B$15,Data!$E$3:$F$3,0)),Data!$A:$A,"P2",Data!$D:$D,$A18)
It's usually preferable to avoid volatile functions like INDIRECT where possible.......
regards, daddylonglegs
