Multiple criteria sum product not working

[Phlegon_of_Tralles]

Okay, I'm trying to count the number of people on a spread sheet who have left in May, who have the first three characters of their separation reason as 'vol'.
Here's my current formula. It's not working.

=SUMPRODUCT(--(ISNUMBER(MATCH(Terminations!$A:$A,"May",0)))*--(ISNUMBER(MATCH(LEFT(Terminations!$B:$B,3)="VOL",0))))

I've attached a spreadsheet with the formula.

Where am I going wrong?

[Special-K]

=SUMPRODUCT((Terminations!$A:$A="May")*(LEFT(Terminations!$B:$B,3)="VOL"))

[AlKey]

Try this

Please Login or Register  to view this content.

[Phlegon_of_Tralles]

Special-K, that worked! Thanks.

[Phlegon_of_Tralles]

Try this

Please Login or Register  to view this content.

This worked too.


I'm curious - the worksheet that i have is going to have A LOT of formulas on it. Do either of you know which of these two formulas would require less resources?

[AlKey]

The COUNTIF or COUNTIFS are much better option than SUMPRODUCT. It is always recommended to use them when possible.

[Phlegon_of_Tralles]

Can you change this so that it looks up a range?

So instead of this:
=COUNTIFS(Terminations!A:A,"May",Terminations!B:B,"VOL*")

It's

=COUNTIFS(Terminations!A:A,"May",WkSht!$B8:$B13) (I realize WkSht isn't on the original document I uploaded).

WHen I try that, it doesn't work. I'm trying to figure out why