Counting cells using multiple parameters

[tuph]

I have a spreadsheet which reports customer sales for the month by
branch, as follows:

cust prefix cust code branch cat date opened last inv date sales avg
mthly sales d-ls-a
03 03cash 3 0 35299 38868 158777 14434.27273 a
03 03cod 3 0 35299 38849 22663 2060.272727 a
03 03po 3 0 35885 0 0 d
ad adiope 3 1 34625 38868 180230 16384.54545 a
ai airfri 3 3 37160 38509 -34 -3.090909091 d
ai airle2 3 8 38491 38608 1559 141.7272727 ls
am amrmax 3 8 36412 38868 39394 3581.272727 a
an anceng 3 8 38813 38868 8962 814.7272727 ls
an anderr 3 8 32721 38426 0 0 d
ap apvc 3 5 38518 38862 625 56.81818182 ls

I need to count the number of customers where Branch=3, CAT=8, and
D-LS-A=A. I thought this formula might do it, but the result is a
#VALUE error:

=IF($I$5:$I$19199,"=A",SUMPRODUCT(($F$5:$F$25000=3)*($G$5:$G$25000=8)))

Thanks in anticipation,

Trish

 

[Chip Pearson]

Try


=SUMPRODUCT(--($I$5:$I$25000="A"),--($F$5:$F$25000=3),--($G$5:$G$25000=8)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





in message
news:[email protected]...

 

[tuph]

Thanks very much, Chip. It's just what I needed.

Trish

 

[sukii21]

Hi there,

I would like to know the implications of the double dash placed before
each array in the sumproduct function, what does it do?

regards
sukii