Countifs with one range being an array

[neilos81]

Hi

I'm looking for some advice on a countifs function that i'm having issues with. I'm wanting to count the total number of specific grades issued to pupils from about 30 subjects (grades stored in about 30 separate columns).
The first criteria range is looking for a particular name in a column (easy) and the second needs to look across multiple columns (about 30) for a specifid grade. I want to count how many matches the criteria.

It works when it is looking at one column at a time =COUNTIFS('Sheet2'!A:A,"SpecifiedName,'Sheet2'!B:B,"SpecifiedGrade") but i need the second criteria to be lookung across 30 columns, so something like:

=COUNTIFS('Sheet2'!A:A,"SpecifiedName,'Sheet2'!B:AE,"SpecifiedGrade")

This is just throwing me a #VALUE! error - i've looked at some previous threads on sumproduct but they give an error saying it has run out of resources..!

Is there an easy and obvious solution? Any help would be muc appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT((Sheet2!A2:A1000="Name")*(Sheet2!B2:AE1000="grade"))

 

[neilos81]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT((Sheet2!A2:A1000="Name")*(Sheet2!B2:AE1000="grade"))

Hi, thanks for the speedy reply, I tried that also and get a processing error, as per the one below. I'm not using a particularly old computer and i get this if only try one column, never mind all 30.

 

[Fluff]

Did you use the formula exactly as I posted, or did you change it?

 

[neilos81]

Did you use the formula exactly as I posted, or did you change it?

Aha, guilty as charged...! I was using entire columns in the range (B:AE), which generally seemd works fine in countif(s). I have now added the rows in and it is working a treat! Thank you so much!

 

[Fluff]

It's always best not to use whole column references as it will slow the formulae down. Especially with array formulae.

 

[neilos81]

noted for future reference. thanks again

 

[Fluff]

Glad to help & thanks for the feedback.