Help with max counts of numbers in multiple columns

[cshrestha]

I am trying to get a max count of multiple columns (C,D,E) on the attached. Currently the formula I have(=MAX(COUNTIFS(Assessment!$A$3:$A$97,J9,Assessment!$C$3:$C$97,">0"),COUNTIFS(Assessment!$A$3:$A$97,J9,Assessment!$D$3:$D$97,">0"),COUNTIFS(Assessment!$A$3:$A$97,J9,Assessment!$E$3:$E$97,">0"))) is giving me 9 counts which is not accurate since the count on the attached should be 10. Thanks for your help.

[modytrane]

Seems to be working fine.
Columns D and E each have 9 entries >0. Column C has one entry >0.
So result of 9 is correct.

[Special-K]

The result is correct 9.

You're looking at all the LFO/TMCs so all the rows from 3:18

The count of the values > 0 in column C is 1 (C13)
The count of the values > 0 in column D is 9
The count of the values > 0 in column D is 9

The MAX of 1, 9, and 9 is 9 not 10.

Why do you think it should be 10 ?

[cshrestha]

Thanks for the quick response. I am trying to get a count of Service Center Name (column B) that had a score higher then 0 (but the identifying criteria is the Region LFO/TMC). Currently the formula is counting max for each column like the way you have shown above but I am trying to get the max for all three columns which I think should be 10 (by counting 9 we are excluding E3 (Central Service Center - CBS Building) and D8 (South Area Transmission Service Center - Fleet Services) count.

[XOR LX]

Hi,

Not sure I understand. You seem to talk both of a "count" and of a "max". Which is it?

Perhaps, array formula**:

=SUM(IF($A$3:$A$97=J9,IF(MMULT(0+($C$3:$E$97>0),TRANSPOSE(COLUMN($C$3:$E$97))^0)>0,1)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[cshrestha]

Thank you so much! That works perfectly. Appreciate your help.

[XOR LX]

No worries.

FYI the equivalent COUNTIFS construction would be a tad inflexible to say the least:

=SUM(COUNTIFS(A:A,J9,C:C,{">0",">0",">0",">0","<=0","<=0","<=0"},D:D,{">0",">0","<=0","<=0",">0",">0","<=0"},E:E,{">0","<=0",">0","<=0",">0","<=0",">0"}))

Regards

[cshrestha]

Thanks again! That formula saved me a lot of time.