|
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 -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=553080 |
Counting cells using multiple parameters
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 |
Counting cells using multiple parameters
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 |
Counting cells using multiple parameters
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 |
| All times are GMT +1. The time now is 02:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com