# Help with max counts of numbers in multiple columns

1. ## Help with max counts of numbers in multiple columns

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.

2. ## Re: Help with max counts of numbers in multiple columns

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.

3. ## Re: Help with max counts of numbers in multiple columns

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 ?

4. ## Re: Help with max counts of numbers in multiple columns

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.

5. ## Re: Help with max counts of numbers in multiple columns

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).

6. ## Re: Help with max counts of numbers in multiple columns

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

7. ## Re: Help with max counts of numbers in multiple columns

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

8. ## Re: Help with max counts of numbers in multiple columns

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1