Countifs / sumifs

[Gwill1983]

Hi all,

I am struggling a little to make a COUNTIFS or SUMIFS formula work on my spreadsheet.

I am trying to count the amount of errors made depending on the category. I have the scores in one tab and the table containing the number of errors in another.

So.... My current formula is below:

=IFERROR(COUNTIFS('Scores - 2019'!A:A,R11,'Scores - 2019'!P:T,"=No"),0)

In essence, the Scores - 2019 tab contains the scores that I am trying to count.

Column A contains the name of the category
R11 is the reference of the category in the current tab
Column P:T in the scores contains the yes/no if there is an error

In the scores table I have adjusted the cells so that 1 shows as Yes and 0 shows as No. Not sure if this is what is stopping the formula.

Currently everything is showing as 0 even though there are some errors to count.

Any help would be much appreciated.

Chris

 

[RoryA]

Can you put a workbook somewhere (eg Dropbox/OneDrive) and post a link here?

 

[Gwill1983]

unfortunately not. I reckon I am on full lock down for uploading things on my work account.

The only issue with the formula that you provided previously is that it appears to be adding all 0's in the sum range rather than only the 0's in the row containing the category. Any ideas why this would be happening?

 

[RoryA]

No - it won't do that. Do you have blank cells as well as 1s and 0s? It would count blank cells as 0.

 

[Gwill1983]

Ah! Yes, there are lots of blanks!
Is there a way of only counting the o's rather than blanks? Or is there anything that you can suggest to adjust this?

 

[RoryA]

You can specify that like this:

=IFERROR(SUMPRODUCT(('Scores - 2019'!$A$1:$A$1000=R11)*('Scores - 2019'!$P$1:$T$1000=0)*('Scores - 2019'!$P$1:$T$1000<>"")),0)