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