How do I create a COUNTIF function with more than one criteria?
 

I have a cell that has a drop down menu with "yes", "no", or "total". I need
to count how many lots are in a particular part of town in another cell, but
it should count depending on the drop down menu. Here is what I need to do:

If the drop down has "yes" selected, I count all the lots on the next
worksheet in Column "A" that has a "yes" in it.
If the drop down has "no" selected, I count all the lots on the next
worksheet in Column "A" that has a "no" in it.
If the drop down has "total" selected, I count all the lots.

I cannot figure out how to give the function multiple criteria.

Tahnks for any help you can give.
BCowans

You can place the value in your drop down into a cell and then reference that
cell in your countif formala. I typically put the value selected in the drop
down in the cell behind the drop down box. For example, if your drop down
box is over cell "F1", then you can put "F1" in the LinkedCell property for
the drop down box. When you select "Yes", "No", or "Total" it will place
that value in cell "F1". Then your countif formula should look as follows:
=countif(range, F1)

"BCowans" wrote:

I have a cell that has a drop down menu with "yes", "no", or "total". I need
to count how many lots are in a particular part of town in another cell, but
it should count depending on the drop down menu. Here is what I need to do:

If the drop down has "yes" selected, I count all the lots on the next
worksheet in Column "A" that has a "yes" in it.
If the drop down has "no" selected, I count all the lots on the next
worksheet in Column "A" that has a "no" in it.
If the drop down has "total" selected, I count all the lots.

I cannot figure out how to give the function multiple criteria.

Tahnks for any help you can give.
BCowans

Hi BCowans
I used Sheet9 to test my formula and Sheet9A for test data (yes/no).
Cells used for data were A1:A500 with a gap from A75:A83 to test behavior of
formula across missing data.
In Sheet9 (formula sheet) Cell A1 = "total"
Cell A2 = Dropdown list
Cell C1 = "=COUNTA(Sheet9A!A1:A500)"
Cell C2 = "=IF(A2=A1,C1,COUNTIF(Sheet9A!A1:A500,Sheet9!A 2))"
Cell E1:E3 = list
I found that it greatly simplified things to create a preloaded COUNTA
function on
the formula page and reference that instead of dealing with it in the actual
COUNTIF function.
See results below:

Col A Col C Col E
total 492 yes
no 281 no
total
total 492 yes
yes 211 no
total
total 492 yes
total 492 no
total

Hope this helps
SongBear

"BCowans" wrote:

I have a cell that has a drop down menu with "yes", "no", or "total". I need
to count how many lots are in a particular part of town in another cell, but
it should count depending on the drop down menu. Here is what I need to do:

If the drop down has "yes" selected, I count all the lots on the next
worksheet in Column "A" that has a "yes" in it.
If the drop down has "no" selected, I count all the lots on the next
worksheet in Column "A" that has a "no" in it.
If the drop down has "total" selected, I count all the lots.

I cannot figure out how to give the function multiple criteria.

Tahnks for any help you can give.
BCowans