Need Some Help with SumProduct

[JKwan]

Attached a sample file.
I would like to count using SumProduct, however, I am not counting it correctly, I don't really know why. Hoping someone can correct my formula.
What I am counting is that highlighted in yellow with the following criteria:
Function Area = Production
Delivery Unit = Canada
Quarter - broken down to Q1 to Q4
Year - 2016
I think here is where I am messing up
OGPTier to be - Tier 1 or Tier 2
OR
LOPCKPISpill = Y

What I have in the sample file counted 14 for Q1 - which is clearly incorrect. I think it should be 8, Q2 should be 8, Q3 should be 5, Q4 should be 10.
Looks to me that it is double counting the LOPC.....

[mancubus]

?

=SUMPRODUCT(($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*(($E$2:$E$1519={"Tier 1"\"Tier 2"})))
+

SUMPRODUCT(($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*($K$2:$K$1519="Y"))
=

=SUMPRODUCT(($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*(($E$2:$E$1519={"Tier 1"\"Tier 2"})))+SUMPRODUCT(($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*($K$2:$K$1519="Y"))

[JKwan]

Thank you Mancubus, however, with your adjusted formula "won't" work very well. Not work very well, meaning if it is Tier 1 or Tier 2, they will be counted twice. With a single SumProduct, this will give me a single count, at least that is what I wanted to do. I don't want to double count.

A big thank you

[mancubus]

perhaps you should define the role of the last OR more clearly.

[JKwan]

Sorry, I thought I explained it clearly. I guess I am wrong. Referring back to initial post, criteria hoping straight forward up to year. Now, I would like to count if it is (Tire 1 or Tier 2) or if it is a KPI ("Y"). The attached file contains the formulae that I am working on, but not working....

Thanks again

[mancubus]

or perhaps i did't understand well. :dunno :rofl:


so you say since Tier 1 and Tier 2 cells are already counted, exclude them when counting Y cells.


=SUMPRODUCT(($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*(($E$2:$E$1519={"Tier 1"\"Tier 2"})))
(count is 2)
+

SUMPRODUCT(($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*($E$2:$E$1519<>"Tier 1")*($E$2:$E$1519<>"Tier 2")*($K$2:$K$1519="Y"))
(count is 5)
=

=SUMPRODUCT(($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*(($E$2:$E$1519={"Tier 1"\"Tier 2"})))+SUMPRODUCT(($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*($E$2:$E$1519<>"Tier 1")*($E$2:$E$1519<>"Tier 2")*($K$2:$K$1519="Y"))
(total count is 7)

there may be a better formula to accomplish this. :hi:

[JKwan]

Mancubus:
I think that is what I am looking for, a great big thank you! Yes, I do believe that there is a better formula, however, I think as long as it works, what the heck. This is the formula that I was working on, but counting wrong, maybe you can see if you can make it work?

=SUMPRODUCT(--($C$2:$C$1519="Production")*($L$2:$L$1519=2016)*($B$2:$B$1519="Canada")*($G$2:$G$1519="Q1")*(($E$2:$E$1519={"Tier 1","Tier 2"})+($K$2:$K$1519="Y")))

My thinking is that - (($E$2:$E$1519={"Tier 1","Tier 2"})+($K$2:$K$1519="Y")) - count if Tier 1 or 2, then count if LOPCKPI = "Y", since I am using the "+" within the SumProduct, that is an OR function, so if it counted the tiers then it should not count LOPCKPI, but the count is wrong.....

Nonetheless, your formula will let me proceed, just wanted to say thanks again.

[mancubus]

http://www.excel-easy.com/examples/count-with-or-criteria.html

perhaps this helps better...