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


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=553080

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





"tuph" wrote
in message
...

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


--
tuph
------------------------------------------------------------------------
tuph's Profile:
http://www.excelforum.com/member.php...o&userid=31390
View this thread:
http://www.excelforum.com/showthread...hreadid=553080

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

Trish


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=553080

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

Chip Pearson wrote:
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





"tuph" wrote
in message
...

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


--
tuph
------------------------------------------------------------------------
tuph's Profile:
http://www.excelforum.com/member.php...o&userid=31390
View this thread:
http://www.excelforum.com/showthread...hreadid=553080