# unable to grasp SUMPRODUCT

1. ## unable to grasp SUMPRODUCT

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

2. ## Sumpproduct

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

3. ## Re: unable to grasp SUMPRODUCT

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" <toddbob.25r98z_1144179903.427@excelforum-nospam.com> wrote in message
news:toddbob.25r98z_1144179903.427@excelforum-nospam.com...
>
> 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
>

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

5. ## RE: unable to grasp SUMPRODUCT

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
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1