Countif resulting in wrong results

[Krameht]

Hello

I'm trying to count whether there is a W in column G only if there is a 1 in column R

the correct answer is 3

=SUMPRODUCT((R27:R="1")*(G27:G="W")) results in 0

=COUNTIF(AND(R27:R="1",G27:G="W")) is NA

=COUNT(IF(G27:G="W",IF(R27:R="1"))) is 0

=SUM(IF(R27:R="1",IF(G27:G="W"))) is #Value

=IF(R27:R="1",COUNTIF(G27:G="W")) is #Value

=COUNTIFS(R27:R="1", G27:G="W") is 1



In addition to this once the correct value is found I need to divide it by the number of "1"s in column R

[Czeslaw]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mcmahobt]

To add on to Czeslaw's response, his formulas are correct, but it is only returning a value of 2 because one of your values within column R is stored as text, not a value.

Please Login or Register  to view this content.

will pickup the 1 stored with text, but that does not fix the underlying cause of the problem.

[Krameht]

Ah that's ok I'll just shift all that info into notes where it belongs. A collaborative effort is not necessarily an organised one. Thank you, this has been driving me mad.