Counting cells using multiple parameters

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

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" <[email protected]> wrote
in message
news:[email protected]...
>
> 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

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" <[email protected]> wrote
> in message
> news:[email protected]...
> >
> > 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
> >