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


In article <[email protected]>,
Roy Ringrose <[email protected]> wrote:

> 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