software

trying to use this formula:

SUMPRODUCT(($L$22:$L$1166={quot;1svcquot;})*($M$22:$M$1166 ))

Column L contains both text and numerical data. Using excel help, near
as I can tell it cannot read the column with both types being contained
within.here is as far as I can get with quot;helpquot;:

-Microsoft Excel cannot translate the text into the correct data type.
Make sure the formula or function is correct for the required operand
or argument, and that the cells that are referenced by the formula
contain valid values. For example, if cell A5 contains a number and
cell A6 contains the text quot;Not availablequot;, the formula =A5 A6 will
return the error #VALUE!.-
???--
redneck joe------------------------------------------------------------------------
redneck joe's Profile: www.excelforum.com/member.php...oamp;userid=32570
View this thread: www.excelforum.com/showthread...hreadid=535222
It looks like a viable formula to me - what result do you get?

The curly braces are superfluous here and I'd advise a different
syntax

=SUMPRODUCT(--($L$22:$L$1166=quot;1svcquot;),$M$22:$M$1166)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=535222
Sorry, it is Saturday so I'm a bit slow today, but you really only need
SUMIF for one criterion, i.e.

=SUMIF($L$22:$L$1166,quot;1svcquot;,$M$22:$M$1166)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=535222
gives me #value

I'll try yours in a bit and see what happens.

One thing forgot, the quot;1svcquot; is just one of many variable I will be
looking for (it was just my test) - how to string those in?--
redneck joe------------------------------------------------------------------------
redneck joe's Profile: www.excelforum.com/member.php...oamp;userid=32570
View this thread: www.excelforum.com/showthread...hreadid=535222
thanks - both worked. Why do you prefer one over the other??--
redneck joe------------------------------------------------------------------------
redneck joe's Profile: www.excelforum.com/member.php...oamp;userid=32570
View this thread: www.excelforum.com/showthread...hreadid=535222SUMIF is more efficient if there is only one condition to test for.

If you still need multiple values, use

=SUMPRODUCT(--(ISNUMBER(MATCH($L$22:$L$1166,{quot;1svcquot;,quot;2cdrquot;},0))) ,--$M$22:$M$
1166)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;redneck joequot; gt;
wrote in message
...
gt;
gt; thanks - both worked. Why do you prefer one over the other??
gt;
gt;
gt; --
gt; redneck joe
gt;
gt;
gt; ------------------------------------------------------------------------
gt; redneck joe's Profile:
www.excelforum.com/member.php...oamp;userid=32570
gt; View this thread: www.excelforum.com/showthread...hreadid=535222
gt;

thanks Bob, but i'm back to the #value! error.

????--
redneck joe------------------------------------------------------------------------
redneck joe's Profile: www.excelforum.com/member.php...oamp;userid=32570
View this thread: www.excelforum.com/showthread...hreadid=535222That suggests to me a text value in the M column alongside one of the
conditions tested for.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;redneck joequot; gt;
wrote in message
...
gt;
gt; thanks Bob, but i'm back to the #value! error.
gt;
gt; ????
gt;
gt;
gt; --
gt; redneck joe
gt;
gt;
gt; ------------------------------------------------------------------------
gt; redneck joe's Profile:
www.excelforum.com/member.php...oamp;userid=32570
gt; View this thread: www.excelforum.com/showthread...hreadid=535222
gt;

You could still use SUMIF....

=SUM(SUMIF($L$22:$L$1166,{quot;1svcquot;,quot;xyzquot;},$M$22:$M$1 166))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=535222
And we have a winner....

thank a million for the help.

j

daddylonglegs Wrote:
gt; You could still use SUMIF....
gt;
gt; =SUM(SUMIF($L$22:$L$1166,{quot;1svcquot;,quot;xyzquot;},$M$22:$M$1 166))--
redneck joe------------------------------------------------------------------------
redneck joe's Profile: www.excelforum.com/member.php...oamp;userid=32570
View this thread: www.excelforum.com/showthread...hreadid=535222