| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
don't have a good thread title. (sumproduct?)
trying to use this formula: SUMPRODUCT(($L$22:$L$1166={"1svc"})*($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 "help": -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 "Not available", the formula =A5+A6 will return the error #VALUE!.- ??? -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
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="1svc"),$M$22:$M$1166) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
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,"1svc",$M$22:$M$1166) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
gives me #value I'll try yours in a bit and see what happens. One thing forgot, the "1svc" 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: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
thanks - both worked. Why do you prefer one over the other?? -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
SUMIF 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,{"1svc","2cdr"},0))) ,--$M$22:$M$ 1166) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "redneck joe" wrote in message ... thanks - both worked. Why do you prefer one over the other?? -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
thanks Bob, but i'm back to the #value! error. ???? -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
That 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) "redneck joe" wrote in message ... thanks Bob, but i'm back to the #value! error. ???? -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
You could still use SUMIF.... =SUM(SUMIF($L$22:$L$1166,{"1svc","xyz"},$M$22:$M$1 166)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
And we have a winner.... thank a million for the help. j daddylonglegs Wrote: You could still use SUMIF.... =SUM(SUMIF($L$22:$L$1166,{"1svc","xyz"},$M$22:$M$1 166)) -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
Which means that you have text in a number field as I suggested. If you want
to ignore bad data, so be it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "redneck joe" wrote in message ... And we have a winner.... thank a million for the help. j daddylonglegs Wrote: You could still use SUMIF.... =SUM(SUMIF($L$22:$L$1166,{"1svc","xyz"},$M$22:$M$1 166)) -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
Perhaps it's *mixed* data Bob, which might not necessarily make it *bad*.<g
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message ... Which means that you have text in a number field as I suggested. If you want to ignore bad data, so be it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "redneck joe" wrote in message ... And we have a winner.... thank a million for the help. j daddylonglegs Wrote: You could still use SUMIF.... =SUM(SUMIF($L$22:$L$1166,{"1svc","xyz"},$M$22:$M$1 166)) -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
That may be so RD, but firstly the OP never stated this, so it makes me
think that this is not known, which means a potential error is being overlooked. Secondly, it is summing based upon a condition, which possibly/probably precludes the *mixed* data. There is a strong possibility that there are either text lookie numbers there, or so much data that there is text buried in there. Either way I think the OP should satisfy themselves that it is okay to ignore that data. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ragdyer" wrote in message ... Perhaps it's *mixed* data Bob, which might not necessarily make it *bad*.<g -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... Which means that you have text in a number field as I suggested. If you want to ignore bad data, so be it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "redneck joe" wrote in message ... And we have a winner.... thank a million for the help. j daddylonglegs Wrote: You could still use SUMIF.... =SUM(SUMIF($L$22:$L$1166,{"1svc","xyz"},$M$22:$M$1 166)) -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
don't have a good thread title. (sumproduct?)
I agree with what you're saying, and that's the *exact* reason that I prefer
the asterisk version of SumProduct over the double unary version. The asterisk version *forbids* an inclusion of text by returning the #VALUE! error, while *still* calculating numeric text. The unary version bypasses text entries and allows calculations *excluding* the POSSIBLY incorrect numeric and/or alpha text entry, therefore masking an incorrect conclusion. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message ... That may be so RD, but firstly the OP never stated this, so it makes me think that this is not known, which means a potential error is being overlooked. Secondly, it is summing based upon a condition, which possibly/probably precludes the *mixed* data. There is a strong possibility that there are either text lookie numbers there, or so much data that there is text buried in there. Either way I think the OP should satisfy themselves that it is okay to ignore that data. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ragdyer" wrote in message ... Perhaps it's *mixed* data Bob, which might not necessarily make it *bad*.<g -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... Which means that you have text in a number field as I suggested. If you want to ignore bad data, so be it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "redneck joe" wrote in message ... And we have a winner.... thank a million for the help. j daddylonglegs Wrote: You could still use SUMIF.... =SUM(SUMIF($L$22:$L$1166,{"1svc","xyz"},$M$22:$M$1 166)) -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=535222 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
| SumProduct | Excel Worksheet Functions | |||
| sumproduct causing memory errors? | Excel Worksheet Functions | |||
| Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
