unable to grasp SUMPRODUCT

**toddbob** · 04-04-2006, 03:43 PM

I have different sales offices in sheet1 column E E2 thru E8
I have revision numbers in sheet1 column I I2 thru I8

Data as follows

E2=NF I2=emptycell
E3=DP I3=40619
E4=DP I4=emptycell
E5=NU I5=emptycell
E6=DP I6=40609
E7=DP I7=emptycell
E8=SF I8=40618

Sheet2 column A are my offices

A2=DP
A3=NU
A4=SF
A5=NF

In sheet2 column c I need excel to enter a number to indicate how many times an office gets a revision number. Correct answers for above example should be DP=2 NU=0 SF=1 NF=0. But I cannot seem to get the formula right. I keep getting #NUM or # VALUE and so on. Would appreciate any help thank you.

Todd

**jordun** · 04-04-2006, 04:00 PM

Hi

try:
for DP
=SUMPRODUCT(((Sheet1!$E$2:$E$8)="DP")*1,(ISNUMBER(Sheet1!$I$2:$I$8))*1)

for the others offices replace DP by NU

=SUMPRODUCT(((Sheet1!$E$2:$E$8)="NU")*1,(ISNUMBER(Sheet1!$I$2:$I$8))*1)

and so on. Or better, use an cell reference.

Please note the "*1" (multiply by 1). That's to help Excel to convert the TRUE (or FALSE) result of the (Sheet1!$E$2:$E$8)="DP" evaluation to a numeric expresion (1 or 0) that can be added by sumproduct

Conan Kelly · 04-04-2006, 04:20 PM

toddbob,

Try this:

Enter this formula in B2 on Sheet2 and Fill/Copy down
=SUMPRODUCT((Sheet1!$E$2:$E$8=A2)*(Sheet1!$I$2:$I$8<>"")*1)

I don't think that this is how the SUMPRODUCT function was meant to be used, but we use it this way all the time. Essentially this
is saying "When the cells in the range E2:E8 on Sheet1 are equal to the value in A2 (Sheet2) AND the cells in the range I2:I8 on
Sheet1 are not blank, then count them".

I hope this helps,

Conan Kelly

"toddbob" <[email protected]> wrote in message
news:[email protected]...
>
> I have different sales offices in sheet1 column E E2 thru E8
> I have revision numbers in sheet1 column I I2 thru I8
>
> Data as follows
>
> E2=NF I2=emptycell
> E3=DP I3=40619
> E4=DP I4=emptycell
> E5=NU I5=emptycell
> E6=DP I6=40609
> E7=DP I7=emptycell
> E8=SF I8=40618
>
> Sheet2 column A are my offices
>
> A2=DP
> A3=NU
> A4=SF
> A5=NF
>
> In sheet2 column c I need excel to enter a number to indicate how many
> times an office gets a revision number. Correct answers for above
> example should be DP=2 NU=0 SF=1 NF=0. But I cannot seem to get the
> formula right. I keep getting #NUM or # VALUE and so on. Would
> appreciate any help thank you.
>
> Todd
>
>
> --
> toddbob
> ------------------------------------------------------------------------
> toddbob's Profile: http://www.excelforum.com/member.php...o&userid=33112
> View this thread: http://www.excelforum.com/showthread...hreadid=529773
>

**toddbob** · 04-04-2006, 10:43 PM

Both replys were very helpful and I have achieved what I wanted to do with your supplied formulas, Thank You

JMB · 04-04-2006, 10:50 PM

for more information

http://xldynamic.com/source/xld.SUMPRODUCT.html

"toddbob" wrote:

>
> I have different sales offices in sheet1 column E E2 thru E8
> I have revision numbers in sheet1 column I I2 thru I8
>
> Data as follows
>
> E2=NF I2=emptycell
> E3=DP I3=40619
> E4=DP I4=emptycell
> E5=NU I5=emptycell
> E6=DP I6=40609
> E7=DP I7=emptycell
> E8=SF I8=40618
>
> Sheet2 column A are my offices
>
> A2=DP
> A3=NU
> A4=SF
> A5=NF
>
> In sheet2 column c I need excel to enter a number to indicate how many
> times an office gets a revision number. Correct answers for above
> example should be DP=2 NU=0 SF=1 NF=0. But I cannot seem to get the
> formula right. I keep getting #NUM or # VALUE and so on. Would
> appreciate any help thank you.
>
> Todd
>
>
> --
> toddbob
> ------------------------------------------------------------------------
> toddbob's Profile: http://www.excelforum.com/member.php...o&userid=33112
> View this thread: http://www.excelforum.com/showthread...hreadid=529773
>
>