+ Reply to Thread
Results 1 to 35 of 35

Multi-Column Counting - COUNTA or other Options

  1. #1
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Multi-Column Counting - COUNTA or other Options

    I'm having a devil of a time with a function that will allow to tally numbers across three columns. I want to count a row if any one of three columns has any information in it, then tally the total for up to 10,000 rows.

    Example, three columns, 4 rows:

    HPhone, WPhone, MPhone
    427-8458, ,
    426-1543, , 213-7865
    , ,
    325-1029, 302-0987, 908-7654


    I want to count the number of rows that have any entry in any of the three columns and I could have up to 10,000 rows to total.

    The answer that should result from the example above is 3. I've tried numerous COUNTA and SUMPRODUCT options, but none of them are working out. Any advice?

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Multi-Column Counting - COUNTA or other Options

    Hi argomike and welcome to the forum,

    See if the attached isn't what you want.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multi-Column Counting - COUNTA or other Options

    The answer is what I'm looking for, but I don't want to have to create a fourth column to keep running totals of each row, if that makes sense.

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Multi-Column Counting - COUNTA or other Options

    Hi ArgoMike,

    Using SUmproduct I got it as 9..
    but I think u need it RowWise and not Cell-Wise..

    PHP Code: 
    =SUMPRODUCT(--(A2:C8<>""
    Warm regards
    e4excel

  5. #5
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Multi-Column Counting - COUNTA or other Options

    I think this ouwld be possible too..!

    Like Iterating the COunt for each Matrix of 2 Coulumns X 1 Row and then counting it for the & Rows..

    But I am not sure how to go about that..

    Methinks that it would be possible with some ROW(INDIRECT(??&":"??)) coding..

  6. #6
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Multi-Column Counting - COUNTA or other Options

    Dear ArgoMike,

    I think this approach would work..

    PHP Code: 
    =SUMPRODUCT(--(ROW(INDIRECT(2&":"&7))<>"")) 
    But still trying to figure how to accomodate the Columns

    Warm Regards
    e4excel
    Last edited by e4excel; 01-25-2012 at 06:25 AM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Multi-Column Counting - COUNTA or other Options

    Hi Argomike,

    You can do it in a single formula if you count the number of rows in your range and subtract from that the rows that contain 3 blank cells, something like:

    =ROWS(A2:A9)-SUMPRODUCT((A2:A9="")*(B2:B9="")*(C2:C9=""))

    Obviously the 9 in my formula will be 10000 or whatever in your real file.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Multi-Column Counting - COUNTA or other Options

    Basically, if it were possible to iterate the functionality 7 times for a Single Data Range and also save the count then we will get the answers for the requirement..

    Basically,


    =SUMPRODUCT(--(An:Cn<>"")) and this has to be iterated 7 Times

    where n = is the Row Number

    Warm Regards
    e4excel

  9. #9
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Multi-Column Counting - COUNTA or other Options

    =SUMPRODUCT(SIGN((A2:A9<>"")+(B2:B9<>"")+(C2:C9<>"")))
    You will need to adjust the ranges to match your data.
    Good luck.

  10. #10
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multi-Column Counting - COUNTA or other Options

    Pete,

    This one doesn't seem to be working. I get the following error: "Range has no entry corresponding to the cell"


    Quote Originally Posted by Pete_UK View Post
    Hi Argomike,

    You can do it in a single formula if you count the number of rows in your range and subtract from that the rows that contain 3 blank cells, something like:

    =ROWS(A2:A9)-SUMPRODUCT((A2:A9="")*(B2:B9="")*(C2:C9=""))

    Obviously the 9 in my formula will be 10000 or whatever in your real file.

    Hope this helps.

    Pete

  11. #11
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multi-Column Counting - COUNTA or other Options

    Getting the same error I got with Pete, and there is data in that range.

    Quote Originally Posted by OnErrorGoto0 View Post
    =SUMPRODUCT(SIGN((A2:A9<>"")+(B2:B9<>"")+(C2:C9<>"")))
    You will need to adjust the ranges to match your data.

  12. #12
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multi-Column Counting - COUNTA or other Options

    Getting the same error I got with Pete, and there is data in that range.

    Quote Originally Posted by OnErrorGoto0 View Post
    =SUMPRODUCT(SIGN((A2:A9<>"")+(B2:B9<>"")+(C2:C9<>"")))
    You will need to adjust the ranges to match your data.

  13. #13
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multi-Column Counting - COUNTA or other Options

    I'm thinking I may have to create a hidden column to run subtotals, similar to what Marvin proposed, but i'd prefer not to do that if anyone has ideas.

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Multi-Column Counting - COUNTA or other Options

    Quote Originally Posted by argomike View Post
    Pete,

    This one doesn't seem to be working. I get the following error: "Range has no entry corresponding to the cell"
    You know, I've never come across that particular error message before. It worked for me in the test file, so I can only assume that you have introduced some typing error. Obviously, you should not place that formula within any of the ranges that it covers (i.e. put it in column D somewhere) and you should check that you have typed it out correctly as there are a lot of brackets in there. To avoid typing mistakes, copy the formula from the above post and paste it into the formula bar with a cell in column D selected. Then make the adjustments to the ranges to suit your data.

    Hope this helps.

    Pete

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Multi-Column Counting - COUNTA or other Options

    Quote Originally Posted by argomike View Post
    Pete,

    This one doesn't seem to be working. I get the following error: "Range has no entry corresponding to the cell"
    You know, I've never come across that particular error message before. It worked for me in the test file, so I can only assume that you have introduced some typing error. Obviously, you should not place that formula within any of the ranges that it covers (i.e. put it in column D somewhere) and you should check that you have typed it out correctly as there are a lot of brackets in there. To avoid typing mistakes, copy the formula from the above post and paste it into the formula bar with a cell in column D selected. Then make the adjustments to the ranges to suit your data.

    Hope this helps.

    Pete

  16. #16
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Multi-Column Counting - COUNTA or other Options

    Hi argomike,

    See the attached.
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  17. #17
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Multi-Column Counting - COUNTA or other Options

    Helper columns aren't that hard to use. Many times they make problems a lot simpler by breaking them down into smaller pieces.

  18. #18
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Multi-Column Counting - COUNTA or other Options

    ArgomIke,

    Please have look at Haseeb's solution..i think thats the one you are looking out for without a Helper Column however most times as mentioned by MarvinP I agree that Helper columns are helpful to not only break the code but also help in checking errors..


    Good WOrk..Haseeb A..

    @ HaseebA - Can you please explain the code as I never knew that we could also use the Multi_Column Ranges as a Starting Point in an Offset...?
    Off-Late I have also been given to understand that we can also use an Index:Index Function instead of the Offset as the latter is VOlatile..

    SO can this also be modified into an Index:Index approach..

    Would really appreciate that...Thnks in advance..

    Warm Regards
    e4excel

  19. #19
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Multi-Column Counting - COUNTA or other Options

    Dear Forum,

    Basically, if it were possible to iterate the functionality 7 times for a Single Data Range and also save the count then we will get the answers for the requirement..

    PHP Code: 
    =SUMPRODUCT(--(An:Cn<>"")) and this has to be iterated 7 Times

    where n 
    is the Row Number 
    I am not a pro at all but have seen some codes where using the ROW INDIRECT combination its made to create an array for the entire Range..

    So , is something possible in this type of approach or I am just expecting the impossible..

    Warm Regards
    e4excel

  20. #20
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Multi-Column Counting - COUNTA or other Options

    Like Pete, I am surprised by the error, not least because I checked my formula before posting and it does work.

  21. #21
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multi-Column Counting - COUNTA or other Options

    Hassad's is working perfectly. Thanks.

    Now, if I wanted it to take various sections of the sheet and apply the formula and then total them together, would I simply change it to this?:

    =SUM((SUMPRODUCT(SIGN(COUNTIF(OFFSET(I7:K32,ROW(I7:I32)-ROW(I7),),"<>"))))+(SUMPRODUCT(SIGN(COUNTIF(OFFSET(I36:K36,ROW(I36:I80)-ROW(I36),),"<>"))))+(SUMPRODUCT(SIGN(COUNTIF(OFFSET(I84:K109,ROW(I84:I109)-ROW(I84),),"<>")))))

    The original formula (for just one section of the sheet), was this:
    =SUMPRODUCT(SIGN(COUNTIF(OFFSET(I7:K32,ROW(I7:I32)-ROW(I7),),"<>")))


    The original formula totaled a 3. However, when I try to total the three sections, it's coming up with a 26. It should be totaling a 3, as there is no data in any of the other two sections. I can post the data, if needed.

  22. #22
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Multi-Column Counting - COUNTA or other Options

    FYI, here is Haseeb's sheet with my formula beneath his.
    Attached Files Attached Files

  23. #23
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Multi-Column Counting - COUNTA or other Options

    One way:

    =SUMPRODUCT(SIGN(COUNTIF(OFFSET(I7:K7,ROW(I7:I32)-ROW(I7),),"<>")))+SUMPRODUCT(SIGN(COUNTIF(OFFSET(I36:K36,ROW(I36:I80)-ROW(I36),),"<>")))+SUMPRODUCT(SIGN(COUNTIF(OFFSET(I84:K84,ROW(I84:I109)-ROW(I84),),"<>")))

  24. #24
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multi-Column Counting - COUNTA or other Options

    OnError,

    Thanks. A simpler way of expressing Habeeb's formula, but when i tried to have it calculate three separate areas from the sheet using the formula below, it gave me #NA error.

    I can't figure how to change the code to accomplish it. I'm changing the wrong component of the code and i'm not sure what.

    =SUMPRODUCT((SIGN((I7:I32<>"")+(J7:J32<>"")+(K7:K32<>"")))+(SIGN((I36:I80<>"")+(J36:J80<>"")+(K36:K80<>"")))+(SIGN((I84:I109<>"")+(J84:J109<>"")+(K84:K109<>""))))

  25. #25
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Multi-Column Counting - COUNTA or other Options

    Quote Originally Posted by OnErrorGoto0 View Post
    FYI, here is Haseeb's sheet with my formula beneath his.
    I've taken OnError's sheet and put my formula below his.

    Hope this helps.

    Pete
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    01-25-2012
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multi-Column Counting - COUNTA or other Options

    Pete,

    How would I adjust the cell allocations in order to accomplish my goal of totaling rows from multiple areas of the sheet? I'm not sure what I'd have to edit from your formula.



    Quote Originally Posted by Pete_UK View Post
    I've taken OnError's sheet and put my formula below his.

    Hope this helps.

    Pete

  27. #27
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Multi-Column Counting - COUNTA or other Options

    argomike,

    Didi you try my last suggestion in post#23 ?

  28. #28
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Multi-Column Counting - COUNTA or other Options

    You would need three separate SUMPRODUCT formulas. Frankly, I would recommend helper cells as they will be more efficient, but:

    =SUMPRODUCT(SIGN((I7:I32<>"")+(J7:J32<>"")+(K7:K32<>"")))+SUMPRODUCT(SIGN((I36:I80<>"")+(J36:J80<>"")+(K36:K8 0<>"")))+SUMPRODUCT(SIGN((I84:I109<>"")+(J84:J109<>"")+(K84:K109<>"")))

  29. #29
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Multi-Column Counting - COUNTA or other Options

    Quote Originally Posted by argomike View Post
    Pete,

    How would I adjust the cell allocations in order to accomplish my goal of totaling rows from multiple areas of the sheet? I'm not sure what I'd have to edit from your formula.
    It would help if you described your data layout - in your first post you said you had 10,000 rows!

    With my formula you are counting the number of rows in the range and subtracting from that the rows that have all three cells blank, so applying my formula to the ranges that OnError has used, you would have this:

    =ROWS(A7:A32)-SUMPRODUCT((I7:I32="")*(J7:J32="")*(K7:K32="")) + ROWS(A36:A80)-SUMPRODUCT((I36:I80="")*(J36:J80="")*(K36:K80="")) + ROWS(A84:A109)-SUMPRODUCT((I84:I109="")*(J84:J109="")*(K84:K109=""))

    Hope this helps.

    Pete

  30. #30
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Multi-Column Counting - COUNTA or other Options

    @OnErrorGoto0,

    Helper Cells = Helper Columns? That seems like a great idea!!! Why didn't I suggest that??

  31. #31
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Multi-Column Counting - COUNTA or other Options

    I am fairly sure you did. Perhaps check your earlier posts.

  32. #32
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Multi-Column Counting - COUNTA or other Options

    I'm sorry but I have a sarcastic streak in me. I did know I suggested it.

    It confuses me why some OPs want a single formula that is very long, normally SumProduct or CSE instead of two small and simple formulas, with a helper column or cell. I normally stop giving answers when the OP says "That works really well, but can you do it in a different way". I feel good that I could solve their problem at all and then they want it solved a different way? WTF?

    I give Pivot Table answers for a lot of questions and sometimes the response is "I don't like Pivot Tables, can you do it differently?". The important word in their response is "LIKE". It is very hard to pick out the esthetic values an OP has when they ask a question. Once again, I feel good that I can get any (correct) answer for their problem and let them deal with their own esthetics.
    Last edited by MarvinP; 01-28-2012 at 02:40 PM.

  33. #33
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Multi-Column Counting - COUNTA or other Options

    Quote Originally Posted by MarvinP View Post
    I'm sorry but I have a sarcastic streak in me.
    As do I, and I tend to meet sarcasm with sarcasm.

  34. #34
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Multi-Column Counting - COUNTA or other Options

    Hey OnErrorGoto0,

    I'm glad we are on the same page. It's great to find a kindred soul with a sense of humor close to my own.

    ALTHOUGH, with your alias name, I'm not sure you can redirect problems to a sarcastic mode. See:
    http://www.cpearson.com/excel/ErrorHandling.htm and read the sentence:
    When On Error Goto 0 is in effect, it is the same as having no enabled error handler.

    I, on the other hand with a cartoon character as my alias, I can (am supposed too?) be slightly funny with my comments. See
    http://forum.bcdb.com/forum/Marvin_t...Quotes_P49387/ with the statement of:
    About the earth "I'm going to blow it up. It obstructs my view of Venus."

    In all seriousness - I'm very glad you volunteer your time and effort to "Pay It Forward" and help others with Excel problems. Once again we are in the same camps.

  35. #35
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Multi-Column Counting - COUNTA or other Options

    It would be a strange choice of moniker if I did not know what it meant.

+ 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