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

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


--
jordun


------------------------------------------------------------------------
jordun's Profile: http://www.excelforum.com/member.php...o&userid=33118
View this thread: http://www.excelforum.com/showthread...hreadid=529773

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" wrote in message
...

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

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


--
toddbob
------------------------------------------------------------------------
toddbob's Profile: http://www.excelforum.com/member.php...o&userid=33112
View this thread: http://www.excelforum.com/showthread...hreadid=529773

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