Chain of multiple COUNTIFS - any way of using a range or something faster?

[Googlyhead]

Hi.
I have a whole bunch of output indicators that increment based on matching two conditions in two columns of data.
Simple enough to do where "if cell1=x and cell2=y, count", but i hit a problem when the match criteria (ie. "y") is a range of values.
Example of what works:

Please Login or Register  to view this content.

You may note that Calculations!$H$2 to Calculations!$H$5 is the range I'm searching for in column G

It's fine for just a couple of lookups, but when I'm using this formula in about 30 cells (all looking for different strings), there's a noticable slowdown!

Any way of refining this? Particularly with regard to speed, but I'll settle for simplicity! (Doesn't have to be COUNTIF - is SUMPRODUCT any faster?)

I've tried using a named range; eg.

Please Login or Register  to view this content.

but that doesn't give the same output (presumably it's looking for ALL the values?)

Thanks for any suggestions! :-)

[daddylonglegs]

You can use a single COUNTIFS with the whole range as the criteria - that will return an "array" of results, one for each value in the range, so you then use a function like SUMPRODUCT to sum that array, i.e.

=SUMPRODUCT(COUNTIFS($H:$H,$A24,$G:$G,Calculations!$H$2:$H$5))

[Googlyhead]

Thanks. That looked a lot better.
I was still hoping for an option that used a range, so I tried it anyway. Then I tried it with a named range and it worked! so perfect!

Please Login or Register  to view this content.

Main reason for preferring a named range was so it's simpler to move the criteria around. The speed I'll have to live with!

Thanks again! :-)