sum product if criteria is ..... calc

[Lorna B]

=SUMPRODUCT(('2012 - Data'!B$2:B$999="V01")*('2012 - Data'!CP$2:CP$999="Strongly Agree"))

I have the above calculation which is working but I want to change this to ask if the first range to say if it is anyone of these three critera and a strongly agree then count it.

See calculation below which I have nused but is not working.

=SUMPRODUCT(('2012 - Data'!B$2:B$999="V01","Manager","Support Staff")*('2012 - Data'!CP$2:CP$999="Strongly Agree"))

Im not too sure where I have gone wrong?

Can someone help?.

Thanks

Lorna

[Fotis1991]

Hi

=SUMPRODUCT(('2012 - Data'!B$2:B$999="V01","Manager","Support Staff")*('2012 - Data'!CP$2:CP$999="Strongly Agree"))

The problem is there(red color) but i am not able to understand your new rule..

Which is this?


Even better if you can upload a small sample workbook for us...

[Cutter]

Try this

=SUMPRODUCT((OR('2012 - Data'!B$2:B$999={"V01","Manager","Support Staff"}))*('2012 - Data'!CP$2:CP$999="Strongly Agree"))


EDIT: I don't think this works

[Cutter]

Or
=SUM(SUMPRODUCT(('2012 - Data'!B$2:B$999="V01")*('2012 - Data'!CP$2:CP$999="Strongly Agree")),SUMPRODUCT(('2012 - Data'!B$2:B$999="Manager")*('2012 - Data'!CP$2:CP$999="Strongly Agree")),SUMPRODUCT(('2012 - Data'!B$2:B$999="Support Staff")*('2012 - Data'!CP$2:CP$999="Strongly Agree")))

[Bob Phillips]

Try this

=SUMPRODUCT((OR('2012 - Data'!B$2:B$999={"V01","Manager","Support Staff"}))*('2012 - Data'!CP$2:CP$999="Strongly Agree"))


EDIT: I don't think this works

You dont need OR

=SUMPRODUCT(('2012 - Data'!B$2:B$999={"V01","Manager","Support Staff"})*('2012 - Data'!CP$2:CP$999="Strongly Agree"))

[Bob Phillips]

Or
=SUM(SUMPRODUCT(('2012 - Data'!B$2:B$999="V01")*('2012 - Data'!CP$2:CP$999="Strongly Agree")),SUMPRODUCT(('2012 - Data'!B$2:B$999="Manager")*('2012 - Data'!CP$2:CP$999="Strongly Agree")),SUMPRODUCT(('2012 - Data'!B$2:B$999="Support Staff")*('2012 - Data'!CP$2:CP$999="Strongly Agree")))

This is much better

=SUMPRODUCT((('2012 - Data'!B$2:B$999="V01")+('2012 - Data'!B$2:B$999="Manager")+('2012 - Data'!B$2:B$999="Support Staff"))*('2012 - Data'!CP$2:CP$999="Strongly Agree"))

[Cutter]

You dont need OR

But I got the curly's. Damn, so close.

This is much better

So much better.

So which version would be most efficient for OP to use?
Or '6 of 1, half dozen of the other'?

[Lorna B]

Perfect - the final one works for me and i understand what I did wrong too. !!

Thanks

Lorna B

[Bob Phillips]

So which version would be most efficient for OP to use?
Or '6 of 1, half dozen of the other'?

I am not sure there would be significant difference, they are all SUMPRODUCT in the end. Personally, I would go for the one that I (the OP) felt was most explanatory in its construct.