• SUMIFS and WEEKNUM

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)

• How Reduce Formula

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........

• countif criteria issue

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)

• Last Working Day of Month

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)

• Finding max value in an array that includes #N/A using VLOOKUP

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"]

• Vlookup / networkdays required

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?

• Converting Date DD-MMM-YYYY to DD/MM/YYYY

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?

• VLookup Is Not Corresponding To Table

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

• COUNTIF the date in one column is within the last 7 days of a date in another cell

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")

• Future date by adding number of days including all days except the days I specify.

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)

• SMALL FUNCTION excluding blank cells

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.........

• Sumproduct severely slowing down spreadsheet

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

• Using countif to count word in specific colums only

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")

• IF function returning 0(blank) instead of TRUE.

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)

• Formula to count unique with 3 criteria.

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........

• Calculate Average of First Lowest Scores over a Timespan

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?

• Search for value and return text in column header

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

• Round up / down date/time format?

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!

• Using IF(COUNTBLANK) Formula but want to exclude cells filled with N/A

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")

• SUMIFS formula w/ variable Sum_Range

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.......