unable to grasp SUMPRODUCT

[toddbob]

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]

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 conver
the TRUE (or FALSE) result of the (Sheet1!$E$2:$E$8)="DP" evaluation t
a numeric expresion (1 or 0) that can be added by sumproduc

 

[Conan Kelly]

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]

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

 

[JMB]

for more information

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