Functions based on multiple criteria

Question

I want to do some count functions based on multiple criteria. I have a sample dataset here:

1. I want to find the number of Males if BD = MCD or FSGS. For this I tried: =COUNTIF(Gender,"M")+COUNTIF(BD,"MCD")+COUNTIF(BD,"FSGS") But am definitely not getting the correct answer.

2. I would also like to count Males if Genetic is Negative. For this I tried: =countif(Gender,"M")+countif(Genetic,"Positive") But also didnt get the correct answer.

Any help would be greatly appreciated!

Answer 1

As eluded by @patkim, you should use COUNTIFS function to obtain the desired results.

To find males whose BDs are MCD, you can try:

=COUNTIFS(Table1[Gender],"=M",Table1[BD],"MCD")

To find males whose Genetics are negative:

=COUNTIFS(Table1[Gender],"=M",Table1[Genetic],"Negative")

Note that I have used structured reference for the table.

Answer 2

Here are some alternative ways to achieve using COUNTIFS():

---

• If the total counts are needed for multiple criteria's then using COUNTIFS()+SUM()

=SUM(COUNTIFS(Gender,"M",BD,{"MCD","FSGS"}))

---

• For Genetic ones:

=COUNTIFS(Gender,"M",Genetic,"Negative")

---

• Also if you structure your output layout like as in E8:G10 then could use the following to spill:

=COUNTIFS(Gender,E9:E10,BD,F8:G8)

---

And if you have access to PIVOTBY() can generate a complete report:

=PIVOTBY(Gender,BD,BD,ROWS,,1)

---

_{NOTE: I have converted the data range into a Structured References aka Tables so as whenever newer data is added the formula gets updated automatically, as well, I have used defined references with names, which you can identify the ranges.}

---

Answer 3

No need for COUNTIFS.
You can use a simple SUM function. In the below formula, I assumed your data was in a Table so I could use structured references, which will automatically adjust to the size of the range:

=SUM((Table1[Gender]="M")*((Table1[BD]="FSGS")+(Table1[BD]="MCD")))

In older versions of Excel, this formula may need to be confirmed as an array formula by holding down ctrl + shift while hitting enter