Count number of columns with value

%3CLINGO-SUB%20id%3D%22lingo-sub-2120274%22%20slang%3D%22en-US%22%3ECount%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120274%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%203rows%20x13%20columns%20with%20the%20following%20data%3A%3C%2FP%3E%3CP%3E1%202%203%204%205%206%207%208%209%2010%2011%2012%3C%2FP%3E%3CP%3E1%202%203%204%3C%2FP%3E%3CP%3E1%205%206%207%208%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsually%2C%20I%20use%20count%20(A1%20to%20A12)%2C%20count%20(B1%3AB12)%20and%26nbsp%3B%20count%20(c1to%20C12)%20into%203%20cells%20and%20use%20anothe%20cell%20to%20determine%20the%20max%20among%20the%203%20formula%20to%20get%2012%26nbsp%3B%20evengthough%20the%20total%20column%20%3D13.%20If%20there%20another%20way%20to%20do%20this%20with%20a%20single%20line%20of%20code%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2120274%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2122355%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2122355%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2122258%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2122258%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121642%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121642%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%2C%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121501%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121501%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20select%20you%20area%3C%2FP%3E%0A%3CP%3E-%20in%20Name%20Box%20(top%20left%20from%20the%20grid)%20type%20%3CSTRONG%3ERange%3C%2FSTRONG%3E%20and%20press%20Enter%3C%2FP%3E%0A%3CP%3E-%20enter%20the%20formula%20in%20suitable%20empty%20cell%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMAX(MMULT(--(Range%26lt%3B%26gt%3B%22%22)%2CTRANSPOSE(COLUMN(Range)%2FCOLUMN(Range))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20642px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F254145i5EEE19327BE9DD75%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121244%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sure%20something%20is%20possible%20there%2C%20but%20nothing%20is%20coming%20immediately%20to%20mind.%20It%20would%20have%20been%20helpful%20had%20you%20spelled%20out%20the%20full%20need%20at%20the%20start%2C%20by%20the%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20I'll%20give%20it%20some%20more%20thought%20and%20try%20to%20get%20back%20to%20you%20in%20a%20day%20or%20two.%20In%20the%20meantime%2C%20maybe%20somebody%20else%20will%20jump%20in%20and%20help%20us%20both.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121090%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121090%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3BI%20have%20close%20to%2040%20excel%20files%20with%20varying%20areas.%20So%2C%20to%20do%20each%20row%20range%20%2C%20is%20very%20tedious%20and%20time%20consuming.%20Imagine%2C%20for%201%20file%2C%20I%20have%20to%20select%2010%20times%20for%2010%20rows.%20Alternatively%2C%20selecting%20the%20area%20is%20only%201%20time%20activity.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121084%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BAssuming%20the%20area%20to%20count%20is%20column%20A%20to%20M.%20Instead%20of%20having%20to%20define%20each%20row%20(A4%3A%20M4)%2C%20is%20it%20possible%20to%20do%20the%20same%20count%20by%20area%20(A4%3AM6)%20and%20the%20outcome%20is%2012%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120383%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20number%20of%20columns%20with%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120383%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20may%20have%20laid%20my%20spreadsheet%20out%20differently%20(in%20rows)%2C%20but%20a%20single%20formula%20is%20quite%20easy%3A%3C%2FP%3E%3CP%3E%3DMAX(COUNT(A4%3AL4)%2CCOUNT(A5%3AL5)%2CCOUNT(A6%3AL6))%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1612972828510.png%22%20style%3D%22width%3A%20698px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253931iCE474941788A2E74%2Fimage-dimensions%2F698x260%3Fv%3D1.0%22%20width%3D%22698%22%20height%3D%22260%22%20role%3D%22button%22%20title%3D%22mathetes_0-1612972828510.png%22%20alt%3D%22mathetes_0-1612972828510.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20you%20wanted%20to%20get%20the%20largest%20sum%20of%20the%20three%20rows%2C%20just%20change%20COUNT%20to%20SUM.%20Etc.%3C%2FP%3E%3CP%3EChanging%20MAX%20to%20MIN%20would%20get%20the%20smallest.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I have 3rows x13 columns with the following data:

1 2 3 4 5 6 7 8 9 10 11 12

1 2 3 4

1 5 6 7 8

 

Usually, I use count (A1 to A12), count (B1:B12) and  count (c1to C12) into 3 cells and use anothe cell to determine the max among the 3 formula to get 12  evengthough the total column =13. If there another way to do this with a single line of code?

8 Replies

@Chan_Tze_Leong 

 

I may have laid my spreadsheet out differently (in rows), but a single formula is quite easy:

=MAX(COUNT(A4:L4),COUNT(A5:L5),COUNT(A6:L6))

mathetes_0-1612972828510.png

 

if you wanted to get the largest sum of the three rows, just change COUNT to SUM. Etc.

Changing MAX to MIN would get the smallest.

 

 

@mathetes Assuming the area to count is column A to M. Instead of having to define each row (A4: M4), is it possible to do the same count by area (A4:M6) and the outcome is 12?

@Chan_Tze_Leong I have close to 40 excel files with varying areas. So, to do each row range , is very tedious and time consuming. Imagine, for 1 file, I have to select 10 times for 10 rows. Alternatively, selecting the area is only 1 time activity.  

@Chan_Tze_Leong 

 

I'm sure something is possible there, but nothing is coming immediately to mind. It would have been helpful had you spelled out the full need at the start, by the way.

 

Anyway, I'll give it some more thought and try to get back to you in a day or two. In the meantime, maybe somebody else will jump in and help us both.

@Chan_Tze_Leong 

- select you area

- in Name Box (top left from the grid) type Range and press Enter

- enter the formula in suitable empty cell

=MAX(MMULT(--(Range<>""),TRANSPOSE(COLUMN(Range)/COLUMN(Range))))

image.png 

thanks,@Sergei Baklan 

 

 

Thank you very much @Sergei Baklan 

@Chan_Tze_Leong , glad to help