Counting Rows Meeting Multiple Criteria with a Formula

[AnyaK]

Hi,

I have a table with various columns.

• Cells in Column A can contain one of "First", "Same", "Last", "Unique".
• Cells in column B specify whether or not a client is homeless. So cells in this column can contain any one of "Homeless", "Not Homeless", "N/A"
• Finally, Cells in column C specify whether an individual has a health need. So each cell could contain any one of "Health Need", "No Need", "N/A".

I'd like to count, for example, how many clients are homeless and have a health need and are either first or unique.

I've tried:
=SUMPRODUCT((OR(ISNUMBER(SEARCH({"First","Unique"},AMCN!$BD$2:AMCN!$BD$1558))))*(ISNUMBER(SEARCH("*Health Need*",AMCN!$BI$2:AMCN!$BI$1558)))*(ISNUMBER(SEARCH("*Homeless*",AMCN!$BO$2:AMCN!$BO$1558))))

and I seem to get either VALUE or N/A errors, or I get numbers that are clearly far to high, i.e. greater than the total number of clients in the spreadsheet!


I also need to count all variations of the above, so for example, are not homeless and have a health need and are either first or unique.
=SUMPRODUCT((OR(ISNUMBER(SEARCH({"First","Unique"},AMCN!$BD$2:AMCN!$BD$1558))))*(ISNUMBER(SEARCH("*Health Need*",AMCN!$BI$2:AMCN!$BI$1558)))*(not(ISNUMBER(SEARCH("*Homeless*",AMCN!$BO$2:AMCN!$BO$1558)))))

And I have the same problem, i.e. VALUE or N/A errors or numbers that are too high.


I've also tried COUNTIFS but had the same problems.

And I tried pivot tables but again the numbers were higher than the total of actual clients.


Please, please help! It's got to the point where I'm really down on myself as I feel so stupid. (I have learning difficulties).

Many thanks,

Anya


P.S. I can post an example this evening (UK Time) if need be but I'm at work at present and where this is strictly not permitted, regrettably. Please forgive this.

 

[BlueHornet]

I like to do this in ways that are more visible to me, even if there may be an extra step or two, and even if there may be helper cells or columns on the sheet. (They can always be hidden at the time of a presentation or print, if they get in the way.)

So here's what I've got:

	E	F	G	H	I
First	Homeless	Health needs
Unique	Homeless	Health needs

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]15[/TD]

</tbody>

[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]I2[/TH]
[TD="align: left"]=SUMPRODUCT((( A:A) = E2) * (( B:B) = F2) * (( C:C) = G2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: <a href="]I3[/TH]
[TD="align: left"]=SUMPRODUCT((( A:A) = E3) * (( B:B) = F3) * (( C:C) = G3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: <a href="]I4[/TH]
[TD="align: left"]=SUM( I2:I3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

You could use:

=SUM(COUNTIFS(AMCN!$BD$2:AMCN!$BD$1558,{"*First*","*Unique*"},AMCN!$BI$2:AMCN!$BI$1558,"*Health Need*",AMCN!$BO$2:AMCN!$BO$1558,"*Homeless*"))

=SUM(COUNTIFS(AMCN!$BD$2:AMCN!$BD$1558,{"*First*","*Unique*"},AMCN!$BI$2:AMCN!$BI$1558,"*Health Need*",AMCN!$BO$2:AMCN!$BO$1558,"<>*Homeless*"))

 

[AnyaK]

Hi BlueHornet and RoryA,

Thanks so much, both of these work perfectly!

I really appreciate your help as I was getting pretty frustrated at myself.

Thank you for coming to my rescue.

Anya