Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
Assume the following are in the cells, and I wish to add all values greater
than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
You could test if the value is a number like this:
=SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7)) Charles F. Lawrence Kulchar wrote: Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
=SUMIF(C1:C10,"1")
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "F. Lawrence Kulchar" wrote in message ... Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
BTW you could have used
=SUMPRODUCT(--(C1:C101),C1:C10) but as I shoed, SP is not necessary here. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "F. Lawrence Kulchar" wrote in message ... Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
YES...i know that...
but why does it NOT work w/o a 3rd "bogus" array?? "Die_Another_Day" wrote: You could test if the value is a number like this: =SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7)) Charles F. Lawrence Kulchar wrote: Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
Not very polite to respond like that when someone tries to help.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "F. Lawrence Kulchar" wrote in message ... YES...i know that... but why does it NOT work w/o a 3rd "bogus" array?? "Die_Another_Day" wrote: You could test if the value is a number like this: =SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7)) Charles F. Lawrence Kulchar wrote: Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
but why does it NOT work w/o a 3rd "bogus" array??
Sumproduct only works on arrays of numbers. In your case, You need to convert the first argument to numbers. By creating two arrays of booleans and multiplying them you do that. Bob showed you a way to do it by using two negation operators without a dummy column. this also works =SUMPRODUCT(1*(C2:C71),(C2:C7)) but purportedly the double negation is faster. -- Regards, Tom Ogilvy "F. Lawrence Kulchar" wrote: YES...i know that... but why does it NOT work w/o a 3rd "bogus" array?? "Die_Another_Day" wrote: You could test if the value is a number like this: =SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7)) Charles F. Lawrence Kulchar wrote: Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
This is great as I have previously been using array formulas, but I think
this may make things less susceptible to error. But I have a related question: How might you use this formula inside a VBA function? I know through code that I could step through all the data, but that takes a long time, Excel seems to do this formula very quickly. And when you might have alot, time is critical. The formula = Application.WorksheetFunction.SumProduct((rng1.Val ue = chk1) * (rng2.Value = chk2), rng3) does not seem to work, any suggestions. "Tom Ogilvy" wrote: but why does it NOT work w/o a 3rd "bogus" array?? Sumproduct only works on arrays of numbers. In your case, You need to convert the first argument to numbers. By creating two arrays of booleans and multiplying them you do that. Bob showed you a way to do it by using two negation operators without a dummy column. this also works =SUMPRODUCT(1*(C2:C71),(C2:C7)) but purportedly the double negation is faster. -- Regards, Tom Ogilvy "F. Lawrence Kulchar" wrote: YES...i know that... but why does it NOT work w/o a 3rd "bogus" array?? "Die_Another_Day" wrote: You could test if the value is a number like this: =SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7)) Charles F. Lawrence Kulchar wrote: Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
Activesheet.Evaluate("SumProduct((rng1.Value=chk1) *(rng2.Value=chk2),rng3)"
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mike Jerakis" wrote in message ... This is great as I have previously been using array formulas, but I think this may make things less susceptible to error. But I have a related question: How might you use this formula inside a VBA function? I know through code that I could step through all the data, but that takes a long time, Excel seems to do this formula very quickly. And when you might have alot, time is critical. The formula = Application.WorksheetFunction.SumProduct((rng1.Val ue = chk1) * (rng2.Value = chk2), rng3) does not seem to work, any suggestions. "Tom Ogilvy" wrote: but why does it NOT work w/o a 3rd "bogus" array?? Sumproduct only works on arrays of numbers. In your case, You need to convert the first argument to numbers. By creating two arrays of booleans and multiplying them you do that. Bob showed you a way to do it by using two negation operators without a dummy column. this also works =SUMPRODUCT(1*(C2:C71),(C2:C7)) but purportedly the double negation is faster. -- Regards, Tom Ogilvy "F. Lawrence Kulchar" wrote: YES...i know that... but why does it NOT work w/o a 3rd "bogus" array?? "Die_Another_Day" wrote: You could test if the value is a number like this: =SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7)) Charles F. Lawrence Kulchar wrote: Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
Thanks, Bob. I think that will work. Assuming of course you meant
"Application" "Bob Phillips" wrote: Activesheet.Evaluate("SumProduct((rng1.Value=chk1) *(rng2.Value=chk2),rng3)" -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mike Jerakis" wrote in message ... This is great as I have previously been using array formulas, but I think this may make things less susceptible to error. But I have a related question: How might you use this formula inside a VBA function? I know through code that I could step through all the data, but that takes a long time, Excel seems to do this formula very quickly. And when you might have alot, time is critical. The formula = Application.WorksheetFunction.SumProduct((rng1.Val ue = chk1) * (rng2.Value = chk2), rng3) does not seem to work, any suggestions. "Tom Ogilvy" wrote: but why does it NOT work w/o a 3rd "bogus" array?? Sumproduct only works on arrays of numbers. In your case, You need to convert the first argument to numbers. By creating two arrays of booleans and multiplying them you do that. Bob showed you a way to do it by using two negation operators without a dummy column. this also works =SUMPRODUCT(1*(C2:C71),(C2:C7)) but purportedly the double negation is faster. -- Regards, Tom Ogilvy "F. Lawrence Kulchar" wrote: YES...i know that... but why does it NOT work w/o a 3rd "bogus" array?? "Die_Another_Day" wrote: You could test if the value is a number like this: =SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7)) Charles F. Lawrence Kulchar wrote: Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT...with text values within array
No, I meant Activesheet. What I didn't mean was to keep the .Value
Activesheet.Evaluate("SumProduct((rng1=chk1)*(rng2 =chk2),rng3)") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mike Jerakis" wrote in message ... Thanks, Bob. I think that will work. Assuming of course you meant "Application" "Bob Phillips" wrote: Activesheet.Evaluate("SumProduct((rng1.Value=chk1) *(rng2.Value=chk2),rng3)" -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mike Jerakis" wrote in message ... This is great as I have previously been using array formulas, but I think this may make things less susceptible to error. But I have a related question: How might you use this formula inside a VBA function? I know through code that I could step through all the data, but that takes a long time, Excel seems to do this formula very quickly. And when you might have alot, time is critical. The formula = Application.WorksheetFunction.SumProduct((rng1.Val ue = chk1) * (rng2.Value = chk2), rng3) does not seem to work, any suggestions. "Tom Ogilvy" wrote: but why does it NOT work w/o a 3rd "bogus" array?? Sumproduct only works on arrays of numbers. In your case, You need to convert the first argument to numbers. By creating two arrays of booleans and multiplying them you do that. Bob showed you a way to do it by using two negation operators without a dummy column. this also works =SUMPRODUCT(1*(C2:C71),(C2:C7)) but purportedly the double negation is faster. -- Regards, Tom Ogilvy "F. Lawrence Kulchar" wrote: YES...i know that... but why does it NOT work w/o a 3rd "bogus" array?? "Die_Another_Day" wrote: You could test if the value is a number like this: =SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7)) Charles F. Lawrence Kulchar wrote: Assume the following are in the cells, and I wish to add all values greater than 1. A B C 1 10 2 8 3 5 4 -2 5 abc 6 4 7 12 The following formula works: =SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7)) HOWEVER, if I remove the logical expression (C2:C7<100), then my formula does NOT work because in cell C5 is a text value.. How can I use the sumproduct formula without inputting the unnecessary array...C2:C7<100?? Thamk you, FLKulchar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need sumproduct to return text values... | Excel Worksheet Functions | |||
Sumproduct with text values | Excel Worksheet Functions | |||
Can Sumproduct return Text values | Excel Worksheet Functions | |||
looking a value in an array text values | Excel Worksheet Functions | |||
SUMPRODUCT function for two arrays. Array 1 contains text | Excel Worksheet Functions |