+ Reply to Thread
Results 1 to 16 of 16

Row Counting Based on Several Cells

  1. #1
    Registered User
    Join Date
    09-29-2020
    Location
    Fort Worth, TX
    MS-Off Ver
    2016
    Posts
    8

    Row Counting Based on Several Cells

    Hopefully my question is clear, and I hope the attachment shows up.

    I have a large spreadsheet with about a dozen columns of information. I would like to count how many rows have certain cells that have specific values in them.

    In the attached file, I've listed several houses with several physical characteristics. How could I set up formulas to return values based on select criteria like "How many houses are red brick?" or "How many houses have 2 floors and 4 rooms?"

    Thanks!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    345

    Re: Row Counting Based on Several Cells

    According to your attachment, Copy formula and drag down

    I3 cell formula
    HTML Code: 
    =COUNTIFS(C:C,G3,D:D,H3)
    I7 cell formula
    HTML Code: 
    =COUNTIFS($B$2:$B$13,--LEFT(G7),$E$2:$E$13,--LEFT(H7))
    J10 cell formula
    HTML Code: 
    =COUNTIFS($B$2:$B$13,--LEFT(G10),$D$2:$D$13,H10,$E$2:$E$13,--LEFT(I10))
    Last edited by wk9128; 09-29-2020 at 11:07 PM.

  3. #3
    Registered User
    Join Date
    05-27-2020
    Location
    Thailand
    MS-Off Ver
    2016
    Posts
    1

    Re: Row Counting Based on Several Cells

    Hi,
    With my understanding, I used "sumproduct" formulas to set the criterias what you need type of houses in yellow cells.

    I3=SUMPRODUCT(--($C$1:$C$13=G3),--($D$1:$D$13=H3))
    I4=SUMPRODUCT(--($C$1:$C$13=G4),--($D$1:$D$13=H4))
    I7=SUMPRODUCT(--($B$2:$B$13=--(LEFT(G7,1))),--($E$2:$E$13=--(LEFT(H7,1))))
    I8=SUMPRODUCT(--($B$2:$B$13=--(LEFT(G8,1))),--($E$2:$E$13=--(LEFT(H8,1))))
    and
    I10 =SUMPRODUCT(--($B$2:$B$13=--(LEFT(G10,1))),--($D$2:$D$13=$H$10),--($E$2:$E$13=--(LEFT(I10,1))))

    You can check it by using filter tool.

  4. #4
    Registered User
    Join Date
    09-29-2020
    Location
    Fort Worth, TX
    MS-Off Ver
    2016
    Posts
    8

    Re: Row Counting Based on Several Cells

    Thanks for the quick response, but my querying is much more extensive than what COUNTIFS can return.

    Given 8 columns of data (after the column uniquely identifying each item being characterized by the data), and each cell being possibly only 1 of 2 values, there are 256 different unique combinations of values in a given spreadsheet. Long story short, there are also 6560 unique ways of quantifying the items based on the characteristics - ranging from returning quantities based on all 8 cells associated with each data point or on only a few cells or even just one cell.

    My spreadsheet has thousands of rows of these combinations. Now, barring creating a COUNTIFS for each cell combination, which would be time-prohibitive to say the least, I was wondering if there was a formula short cut for this.

    Let me attach a slightly more intense example spreadsheet (hopefully without violating the forum guidelines).

    In hypothetical2, on the right side where I'm building my analysis spreadsheet, in some instances I'm concerned with combinations based on only a handful of cell's data, and in some instances I'm concerned with all the cells, and in some only a few cells. Now, as alluded to before, with 8 columns, each column showing 1 of 2 variables...there could be up to 6560 different ways to count the items if the items are counted considering all the cell's or only a handful of the cells associated with each item.

    I hope I am not muddying the issue, and if Excel doesn't have a formula or a combination of formulas to handle that task, I'll just live with it. I've just wracked my excel-novice brain for what combination of nested formulas might solve my problem.

    Thanks again!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-29-2020
    Location
    Fort Worth, TX
    MS-Off Ver
    2016
    Posts
    8

    Re: Row Counting Based on Several Cells

    Doesn't SUMPRODUCT return rows that meet any of the criteria? I'm looking for something that will return rows that meet ALL the criteria being queried.

  6. #6
    Registered User
    Join Date
    09-29-2020
    Location
    Fort Worth, TX
    MS-Off Ver
    2016
    Posts
    8

    Re: Row Counting Based on Several Cells

    And in hypothetical 2, I should have clarified on the right side where the analysis is, any blank cell under the query should be interpreted as the variable can be EITHER option, where the filled in cell the variable MUST be the one specified.

    Thanks again!

  7. #7
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    345

    Re: Row Counting Based on Several Cells

    K3 cell formula drag down
    HTML Code: 
    =COUNTIFS(B:B,L3,C:C,M3,D:D,N3,E:E,O3,F:F,P3,G:G,Q3,H:H,R3,I:I,S3)
    OR other method formula but array
    HTML Code: 
    =SUM(--(MMULT(N($B$3:$I$151=$L3:$S3),ROW($1:$8)^0)>=8))
    Last edited by wk9128; 09-30-2020 at 06:22 AM.

  8. #8
    Registered User
    Join Date
    09-29-2020
    Location
    Fort Worth, TX
    MS-Off Ver
    2016
    Posts
    8

    Re: Row Counting Based on Several Cells

    Quote Originally Posted by wk9128 View Post
    K3 cell formula drag down
    HTML Code: 
    =COUNTIFS(B:B,L3,C:C,M3,D:D,N3,E:E,O3,F:F,P3,G:G,Q3,H:H,R3,I:I,S3)
    It returns a "0" for any of the combinations that leaves a cell blank, because the blank doesn't equal the cells from the data on the left.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    Re: Row Counting Based on Several Cells

    You are making life hard for yourself. I've turned your grid into a table, added a count row and slicers. Easy peasy!
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Registered User
    Join Date
    09-29-2020
    Location
    Fort Worth, TX
    MS-Off Ver
    2016
    Posts
    8

    Re: Row Counting Based on Several Cells

    I think this may be the solution for hypothetical2:
    For I4

    =COUNTIFS(B:B,IF(L4>0,L4,"*"),C:C,IF(M4>0,M4,"*"),D:D,IF(N4>0,N4,"*"),E:E,IF(O4>0,O4,"*"),F:F,IF(P4>0,P4,"*"),G:G,IF(Q4>0,Q4,"*"),H:H,IF(R4>0,R4,"*"),I:I,IF(S4>0,S4,"*"))

  11. #11
    Registered User
    Join Date
    09-29-2020
    Location
    Fort Worth, TX
    MS-Off Ver
    2016
    Posts
    8

    Re: Row Counting Based on Several Cells

    Quote Originally Posted by AliGW View Post
    You are making life hard for yourself. I've turned your grid into a table, added a count row and slicers. Easy peasy!
    Ok, now that's cool. Doesn't do what I want in this instance, but certain useful for future purposes. Can you point me to a tutorial for that?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    Re: Row Counting Based on Several Cells

    It does everything you've asked for so far ...

    Just look at the help files - no magic, all built into Excel.

  13. #13
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    345

    Re: Row Counting Based on Several Cells

    K3
    HTML Code: 
    =sum(--(mmult(n(($b$3:$i$151=$l3:$s3)),row($1:$8)^0)=counta($l3:$s3)))

  14. #14
    Registered User
    Join Date
    09-29-2020
    Location
    Fort Worth, TX
    MS-Off Ver
    2016
    Posts
    8

    Re: Row Counting Based on Several Cells

    Thanks for the help everyone. I think it's solved.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    Re: Row Counting Based on Several Cells

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  16. #16
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    345

    Re: Row Counting Based on Several Cells

    Quote Originally Posted by texasaggie0004 View Post
    I think this may be the solution for hypothetical2:
    For I4

    =COUNTIFS(B:B,IF(L4>0,L4,"*"),C:C,IF(M4>0,M4,"*"),D:D,IF(N4>0,N4,"*"),E:E,IF(O4>0,O4,"*"),F:F,IF(P4>0,P4,"*"),G:G,IF(Q4>0,Q4,"*"),H:H,IF(R4>0,R4,"*"),I:I,IF(S4>0,S4,"*"))
    If you still insist on using COUNTIFS, your formula can be simpler

    K3 cell non array formula
    HTML Code: 
    =COUNTIFS(B:B,L3&"*",C:C,M3&"*",D:D,N3&"*",E:E,O3&"*",F:F,P3&"*",G:G,Q3&"*",H:H,R3&"*",I:I,S3&"*")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] counting cells based on value
    By swfarm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-02-2018, 04:54 PM
  2. Counting cells, based on value
    By MagdaLena23 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-25-2018, 02:41 PM
  3. Replies: 4
    Last Post: 06-13-2014, 07:08 PM
  4. [SOLVED] Counting number based on cells that have value
    By Drayde in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2014, 02:45 PM
  5. Counting cells based on values of adjacent cells
    By SpookyAlienX in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-26-2013, 08:20 PM
  6. Counting based on other cells
    By acvitanov in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-14-2012, 11:31 AM
  7. Complicated counting of cells (based on other cells contents)
    By George in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-07-2005, 02:45 PM

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