software

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: www.excelforum.com/member.php...oamp;userid=33112
View this thread: www.excelforum.com/showthread...hreadid=529773
Hi

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

for the others offices replace DP by NU

=SUMPRODUCT(((Sheet1!$E$2:$E$8)=quot;NUquot;)*1,(ISNUMBER( Sheet1!$I$2:$I$8))*1)

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

Please note the quot;*1quot; (multiply by 1). That's to help Excel to convert
the TRUE (or FALSE) result of the (Sheet1!$E$2:$E$8)=quot;DPquot; evaluation to
a numeric expresion (1 or 0) that can be added by sumproduct--
jordun------------------------------------------------------------------------
jordun's Profile: www.excelforum.com/member.php...oamp;userid=33118
View this thread: www.excelforum.com/showthread...hreadid=529773toddbob,

Try this:

Enter this formula in B2 on Sheet2 and Fill/Copy down
=SUMPRODUCT((Sheet1!$E$2:$E$8=A2)*(Sheet1!$I$2:$I$ 8lt;gt;quot;quot;)*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 quot;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 themquot;.

I hope this helps,

Conan Kelly

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

Both replys were very helpful and I have achieved what I wanted to do
with your supplied formulas, Thank You--
toddbob
------------------------------------------------------------------------
toddbob's Profile: www.excelforum.com/member.php...oamp;userid=33112
View this thread: www.excelforum.com/showthread...hreadid=529773for more information

xldynamic.com/source/xld.SUMPRODUCT.html

quot;toddbobquot; wrote:

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