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

 

[aqeelnokia99]

its battter post your some data and what result u want if u want to get batter formula

 

[Gwill1983]

Hi,

Below is an example of the table where I want the errors to be counted.
Column 1 is column R on my sheet, Row 1 is row 4 on my sheet. Columns 2-4 are staff members

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Category 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My other sheet is very difficult to put in here as it is very large.

I will try to summarise below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thurs[/TD]
[TD]Fri[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR="class: grid"]
[TD]Category 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Column 1 is A and 1st row is 2, There are 2 blank column between the categories and the results. The results start in column D row 7 (cell highlighted with Y above)
These results are marked either with a 1 or 0 (1 changes to Yes, 0 changes to No)
This part of the sheet duplicates 52 times (for each week of the year)

I am trying to get cell S4 on the first sheet to show me the number of No's against category 1 in the results spreadsheet for the entire year!

I cannot work out if COUNTIFS or SUMIFS is the correct formula to use!

Chris

 

[RoryA]

What exactly does this mean: "I have adjusted the cells so that 1 shows as Yes and 0 shows as No"?

If you've applied a custom number format, the COUNTIFS formula needs to use the value of the cell as the criteria (I.e. 1 or 0) not the displayed text.

 

[Gwill1983]

Hi Rory,

Exactly right. It is a custom number format, however if I change the "=NO" to "=0" the formula still doesn't work!

 

[RoryA]

Then the cells don't have 0 in them. Are they blank?

 

[Gwill1983]

They are blank until I complete that weeks results. Once I have completed that weeks results then these will be full of 1's or 0's.

Currently I have filled one week and have 3 0's, but my formula doesn't recognise this and returns a count of 0

 

[RoryA]

I've just looked closer at your formula and it doesn't do what you think. The ranges in a COUNTIFS formula must all be the same size and shape. Since yours aren't, the COUNTIFS part returns a #VALUE error, which your IFERROR turns into 0. You'd need a SUMPRODUCT formula - and you really need to avoid using entire column references with this construction, so use something like:

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

 

[Gwill1983]

Thanks Rory.

This works!! Turns out that I had alot more 0's hidden than I thought so just need to fix this for it to make sense!

Thanks for your help

 

[Gwill1983]

Hi all,

Unfortunately this still isn't working and I am determined to sort it (with help hopefully!)

To try to simplify what I am trying to do, I am trying to count the number of 0's in columns D-T if the cell in column A is equal to cell U4 (for example)

I am struggling to put a detailed table into my feeds so I cannot make it clearer than the tables in the trail.

Can anybody help?

The formula that I was supplied with previously returned a number of over 200 when it should be a maximum of 3 with the data entered currently.

I can supply more details if required.

Thanks

Chris