# Row Counting Based on Several Cells

1. ## 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!  Register To Reply

2. ## 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))``  Register To Reply

3. ## 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.  Register To Reply

4. ## 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!  Register To Reply

5. ## 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.  Register To Reply

6. ## 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!  Register To Reply

7. ## 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))``  Register To Reply

8. ## Re: Row Counting Based on Several Cells Originally Posted by wk9128 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.  Register To Reply

9. ## 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!   Register To Reply

10. ## 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,"*"))  Register To Reply

11. ## Re: Row Counting Based on Several Cells Originally Posted by AliGW 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?  Register To Reply

12. ## 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.  Register To Reply

13. ## 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)))``  Register To Reply

14. ## Re: Row Counting Based on Several Cells

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

15. ## Re: Row Counting Based on Several Cells

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.  Register To Reply

16. ## Re: Row Counting Based on Several Cells Originally Posted by texasaggie0004 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&"*")``  Register To Reply