SUMPRODUCT with nested If statement

[Moeey]

Hi everyone,
Hope you're all well.

I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT formula.

The formula is trying to count all the entries in "HRData" table, for the specified year and "Job Band".
If the job band selected is "All Employees" then count all the entries for the specified year.
If the job band selected is not "All Employees" then only count the selected "Job Band" for the specified year.
The formula only seems to work for "All Employees".
There are no "errors" for the formula.

I think the problem lies with the "else" part of the IF statement.
From what I can gather, the "else" part of the IF statement, is only reading one row of the table.

Here's the formula,
=SUMPRODUCT((YEAR(HRData[Start Date])=$A$8)*(IF($B$8="All Employees",TRUE(),HRData[Job Band]=$B$8)))

This is what my "HRData" table looks like.
There are 10,000 entries in the table.

I hope this makes sense.
Thanks for looking.

Mo

 

[Moeey]

The formula seems to be working as expected on everyone's Excel except mine.
See my previous post regarding the formula when evaluated.

 

[Dave Patton]

Insert a few rows above the range of data that you posted.
Copy the formulas that I suggested and then check the results.


Did you try the formula that I suggested?
You need the correct syntax to use the or component.

 

[Dave Patton]

You could use the following; it yields the same results.

T202009c.xlsm
	A	B	C	D
8	2013	All Employees		25
9	2013	MM		10
1dd

Cell Formulas
Range	Formula
D8:D9	D8	=IF(B8="All Employees",SUMPRODUCT(--(YEAR(HRData[Start Date])=A8)),SUMPRODUCT(--(YEAR(HRData[Start Date])=A2),--(HRData[Job Band]=B2)))

 

[Moeey]

You could use the following; it yields the same results.

T202009c.xlsm
	A	B	C	D
8	2013	All Employees		25
9	2013	MM		10
1dd

Cell Formulas
Range	Formula
D8:D9	D8	=IF(B8="All Employees",SUMPRODUCT(--(YEAR(HRData[Start Date])=A8)),SUMPRODUCT(--(YEAR(HRData[Start Date])=A2),--(HRData[Job Band]=B2)))

Dave,
I think I'll use this one

=SUMPRODUCT(--(YEAR(Table2[Start Date])=A5),--(Table2[Job Band]=B5)+(B5="All Employees"))

Very grateful for your work on this, thank you.
Still curious though, as to why my original formula works for others and not me.

 

[Dave Patton]

"Still curious though, as to why my original formula works for others and not me. "

The If Function and/or * syntax with your original formula dictates that it must be Array Entered.
New versions of Excel may consider the formula an array formula and may not require array enter.
N.B. I did not check this with Excel 365.
With "All Employees" , it does not matter; it is equivalent to Count of Dates * 1.

T202009c.xlsm
	A	B	C	D	E	F	G	H
8	2013	All Employees				25	25	25
9	2013	MM				#VALUE!	#VALUE!
10	2013	MM				10	10
1dd

Cell Formulas
Range	Formula
F8:F9	F8	=SUMPRODUCT((YEAR(HRData[Start Date])=A8)*(IF(B8="All Employees",TRUE(),HRData[Job Band]=B8)))
G8:G9	G8	=SUMPRODUCT((YEAR(HRData[Start Date])=A8)*(IF(B8="All Employees",1,HRData[Job Band]=B8)))
H8	H8	=SUMPRODUCT((YEAR(HRData[Start Date])=A8)*1)
F10	F10	=SUMPRODUCT((YEAR(HRData[Start Date])=A10)*(IF(B10="All Employees",TRUE(),HRData[Job Band]=B10)))
G10	G10	=SUMPRODUCT((YEAR(HRData[Start Date])=A10)*(IF(B10="All Employees",1,HRData[Job Band]=B10)))
Press CTRL+SHIFT+ENTER to enter array formulas.