+ Reply to Thread
Results 1 to 8 of 8

Help with max counts of numbers in multiple columns

  1. #1
    Registered User
    Join Date
    05-22-2019
    Location
    West Palm Beach
    MS-Off Ver
    MS office
    Posts
    7

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,431

    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.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    MS Office Excel 2007
    Posts
    6,704

    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 ?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    05-22-2019
    Location
    West Palm Beach
    MS-Off Ver
    MS office
    Posts
    7

    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. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,928

    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).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Registered User
    Join Date
    05-22-2019
    Location
    West Palm Beach
    MS-Off Ver
    MS office
    Posts
    7

    Re: Help with max counts of numbers in multiple columns

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

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,928

    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. #8
    Registered User
    Join Date
    05-22-2019
    Location
    West Palm Beach
    MS-Off Ver
    MS office
    Posts
    7

    Re: Help with max counts of numbers in multiple columns

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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