+ Reply to Thread
Results 1 to 17 of 17

Row Counting Based on Several Cells

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

    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
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Row Counting Based on Several Cells

    According to your attachment, Copy formula and drag down

    I3 cell formula
    Please Login or Register  to view this content.
    I7 cell formula
    Please Login or Register  to view this content.
    J10 cell formula
    Please Login or Register  to view this content.
    Last edited by AliGW; 01-12-2024 at 03:24 AM. Reason: Code tags corrected.

  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
    13

    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
    13

    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
    13

    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
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Row Counting Based on Several Cells

    K3 cell formula drag down
    Please Login or Register  to view this content.
    OR other method formula but array
    Please Login or Register  to view this content.
    Last edited by AliGW; 01-12-2024 at 03:29 AM.

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

    Re: Row Counting Based on Several Cells

    [QUOTE=wk9128;5402639]K3 cell formula drag down
    Please Login or Register  to view this content.
    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.
    Last edited by AliGW; 01-12-2024 at 03:29 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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
    13

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    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
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Row Counting Based on Several Cells

    K3
    Please Login or Register  to view this content.
    Last edited by AliGW; 01-12-2024 at 03:25 AM. Reason: Code tags corrected.

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

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    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
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    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
    Please Login or Register  to view this content.
    Last edited by AliGW; 01-12-2024 at 03:23 AM. Reason: Code tags corrected.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    Re: Row Counting Based on Several Cells

    @wk9128

    Please do NOT use HTML tags for formulae as they do not show anything in the post in some browsers. Use CODE tags instead. I have corrected all of your posts for you.

+ 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1