count of column b when criteria = conditions of both columns a and

[Roy Ringrose]

EXCEL 2003 - I need a formula to "Count" the number of products listed in
column B, C or D for a particular state listed in column a when input the
state in a specific cell. The difference between the numbers in product A vs
product B vs product C columns is the order of preference of "A" over "B"
over "C". I've tried the CountA and DcountA but I'm missing something.
Something along the lines of: Count the number of times product A is listed
for state of TX. Then Product B and so on. I actually have 5 product columns
and several thousand entries for the state since ech state has several cities.

input "TX"
Product A
Product B
Product C

a b c d
State Product A Product B Product C
CA 1 2
TX 1 2
CA 1 2
CA 1 2 3
TX 1 2
TX 1 2
TX 1 2 3

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A2:A10000="TX"),--(B1:B10000<>""))

See

http://www.mcgimpsey.com/excel/doubleneg.html

for an explanation.

OTOH, your situation seems to be right up the Pivot Table alley...

http://peltiertech.com/Excel/Pivots/pivotstart.htm