Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
I'm trying to sum up the values in column K where the value in column X
equals 2021, 2022, 2023, & 2025. Am I doing it wrong? =SUMPRODUCT(--(Main!$F$2:$F$5000=DATE(YEAR(A14),MONTH(A14),DAY( A14))),--(Main!$F$2:$F$5000<=DATE(YEAR(B14),MONTH(B14),DAY( B14))),--(Main!$X$2:$X$5000="2021"),--(Main!$X$2:$X$5000="2022"),--(Main!$X$2:$X$5000="2023"),--(Main!$X$2:$X$5000="2025"),Main!$K$2:$K$5000) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
If A14 and B14 are dates you can eliminate the DATE function and just refer
to the cells themselves. Also, when you enclose numbers in quotes like this: --(Main!$X$2:$X$5000="2021") Excel evaluates them as TEXT. So, in the above expression the formula is looking for the TEXT string 2021. If the values in that range are really numbers text "2021" and numeric 2021 won't match. Try this: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{2021,2022,2023, 2025},0))),Main!$K$2:$K$5000) -- Biff Microsoft Excel MVP "Secret Squirrel" wrote in message ... I'm trying to sum up the values in column K where the value in column X equals 2021, 2022, 2023, & 2025. Am I doing it wrong? =SUMPRODUCT(--(Main!$F$2:$F$5000=DATE(YEAR(A14),MONTH(A14),DAY( A14))),--(Main!$F$2:$F$5000<=DATE(YEAR(B14),MONTH(B14),DAY( B14))),--(Main!$X$2:$X$5000="2021"),--(Main!$X$2:$X$5000="2022"),--(Main!$X$2:$X$5000="2023"),--(Main!$X$2:$X$5000="2025"),Main!$K$2:$K$5000) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Thanks for your help. Those values are in fact text since that's the way they
are extracted from my database. If I was to just add the "" around them within your formula it will still work, correct? Thanks for pointing out the date function also. I didn't realize I left it set up like that since I was pulling the data a different way in an earlier version of this worksheet. "T. Valko" wrote: If A14 and B14 are dates you can eliminate the DATE function and just refer to the cells themselves. Also, when you enclose numbers in quotes like this: --(Main!$X$2:$X$5000="2021") Excel evaluates them as TEXT. So, in the above expression the formula is looking for the TEXT string 2021. If the values in that range are really numbers text "2021" and numeric 2021 won't match. Try this: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{2021,2022,2023, 2025},0))),Main!$K$2:$K$5000) -- Biff Microsoft Excel MVP "Secret Squirrel" wrote in message ... I'm trying to sum up the values in column K where the value in column X equals 2021, 2022, 2023, & 2025. Am I doing it wrong? =SUMPRODUCT(--(Main!$F$2:$F$5000=DATE(YEAR(A14),MONTH(A14),DAY( A14))),--(Main!$F$2:$F$5000<=DATE(YEAR(B14),MONTH(B14),DAY( B14))),--(Main!$X$2:$X$5000="2021"),--(Main!$X$2:$X$5000="2022"),--(Main!$X$2:$X$5000="2023"),--(Main!$X$2:$X$5000="2025"),Main!$K$2:$K$5000) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Those values are in fact text
If I was to just add the "" around them within your formula it will still work, correct? Yes: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{"2021","2022"," 2023","2025"},0))),Main!$K$2:$K$5000) -- Biff Microsoft Excel MVP "Secret Squirrel" wrote in message ... Thanks for your help. Those values are in fact text since that's the way they are extracted from my database. If I was to just add the "" around them within your formula it will still work, correct? Thanks for pointing out the date function also. I didn't realize I left it set up like that since I was pulling the data a different way in an earlier version of this worksheet. "T. Valko" wrote: If A14 and B14 are dates you can eliminate the DATE function and just refer to the cells themselves. Also, when you enclose numbers in quotes like this: --(Main!$X$2:$X$5000="2021") Excel evaluates them as TEXT. So, in the above expression the formula is looking for the TEXT string 2021. If the values in that range are really numbers text "2021" and numeric 2021 won't match. Try this: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{2021,2022,2023, 2025},0))),Main!$K$2:$K$5000) -- Biff Microsoft Excel MVP "Secret Squirrel" wrote in message ... I'm trying to sum up the values in column K where the value in column X equals 2021, 2022, 2023, & 2025. Am I doing it wrong? =SUMPRODUCT(--(Main!$F$2:$F$5000=DATE(YEAR(A14),MONTH(A14),DAY( A14))),--(Main!$F$2:$F$5000<=DATE(YEAR(B14),MONTH(B14),DAY( B14))),--(Main!$X$2:$X$5000="2021"),--(Main!$X$2:$X$5000="2022"),--(Main!$X$2:$X$5000="2023"),--(Main!$X$2:$X$5000="2025"),Main!$K$2:$K$5000) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Those values are in fact text
If I was to just add the "" around them within your formula it will still work, correct? Yes: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{"2021","2022"," 2023","2025"},0))),Main!$K$2:$K$5000) Can't the 3rd term inside your SUMPRODUCT, which is this... --(ISNUMBER(MATCH([Main.xls]Sheet1!$X$2:$X$5000,{"2021","2022","2023","2025"}, 0))) be simplified to this... --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) (since it would occur inside a SUMPRODUCT function)? Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? Rick |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Can't the 3rd term inside your SUMPRODUCT ... be simplified to this...
--([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) No. The logic of ISNUMBER(MATCH(...)) is OR(cell_ref={"2021","2022","2023","2025"}) --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) Is the same as AND(cell_ref={"2021","2022","2023","2025"}) Since a single cell won't contain all of the variables you'll end up with a #VALUE! error because the evaluated arrays are not the same size. Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? No, I didn't. What address did you use? biffinpitt is a bogus address. A good address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Those values are in fact text If I was to just add the "" around them within your formula it will still work, correct? Yes: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{"2021","2022"," 2023","2025"},0))),Main!$K$2:$K$5000) Can't the 3rd term inside your SUMPRODUCT, which is this... --(ISNUMBER(MATCH([Main.xls]Sheet1!$X$2:$X$5000,{"2021","2022","2023","2025"}, 0))) be simplified to this... --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) (since it would occur inside a SUMPRODUCT function)? Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? Rick |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Yes, I get the #VALUE! error if I use your comma-separated form of the
SUMPRODUCT formula; however, if you use the multiplication form of it (which is my personal preference), this formula... =SUMPRODUCT((Main!$F$2:$F$5000=A14)*(Main!$F$2:$F $5000<=B14)*(Main!$X$2:$X$5000={2021,2022,2023,202 5})*Main!$K$2:$K$5000) which uses the change I suggested, appears to produce the exact same answers as the formula you posted; although I am still just a little too newly returned to Excel after my long absences from it to understand why. OT: Be on the look-out for the email I just sent to you. Rick "T. Valko" wrote in message ... Can't the 3rd term inside your SUMPRODUCT ... be simplified to this... --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) No. The logic of ISNUMBER(MATCH(...)) is OR(cell_ref={"2021","2022","2023","2025"}) --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) Is the same as AND(cell_ref={"2021","2022","2023","2025"}) Since a single cell won't contain all of the variables you'll end up with a #VALUE! error because the evaluated arrays are not the same size. Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? No, I didn't. What address did you use? biffinpitt is a bogus address. A good address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Those values are in fact text If I was to just add the "" around them within your formula it will still work, correct? Yes: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{"2021","2022"," 2023","2025"},0))),Main!$K$2:$K$5000) Can't the 3rd term inside your SUMPRODUCT, which is this... --(ISNUMBER(MATCH([Main.xls]Sheet1!$X$2:$X$5000,{"2021","2022","2023","2025"}, 0))) be simplified to this... --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) (since it would occur inside a SUMPRODUCT function)? Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? Rick |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Yes, the multiplication form does work but the ISNUMBER(MATCH(...)) method
is better. I haven't done a calc time test, though. In the (...)*(...) form you're evaluating each element (cell_ref) of the array against *all* 4 variables. This returns 4 intermediate results for each element. The ISNUMBER(MATCH(...)) method evaluates each element of the array against *any* of the variables and returns only a single intermediate result for each element. So, logic tells me that this should be more efficient. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Yes, I get the #VALUE! error if I use your comma-separated form of the SUMPRODUCT formula; however, if you use the multiplication form of it (which is my personal preference), this formula... =SUMPRODUCT((Main!$F$2:$F$5000=A14)*(Main!$F$2:$F $5000<=B14)*(Main!$X$2:$X$5000={2021,2022,2023,202 5})*Main!$K$2:$K$5000) which uses the change I suggested, appears to produce the exact same answers as the formula you posted; although I am still just a little too newly returned to Excel after my long absences from it to understand why. OT: Be on the look-out for the email I just sent to you. Rick "T. Valko" wrote in message ... Can't the 3rd term inside your SUMPRODUCT ... be simplified to this... --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) No. The logic of ISNUMBER(MATCH(...)) is OR(cell_ref={"2021","2022","2023","2025"}) --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) Is the same as AND(cell_ref={"2021","2022","2023","2025"}) Since a single cell won't contain all of the variables you'll end up with a #VALUE! error because the evaluated arrays are not the same size. Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? No, I didn't. What address did you use? biffinpitt is a bogus address. A good address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Those values are in fact text If I was to just add the "" around them within your formula it will still work, correct? Yes: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{"2021","2022"," 2023","2025"},0))),Main!$K$2:$K$5000) Can't the 3rd term inside your SUMPRODUCT, which is this... --(ISNUMBER(MATCH([Main.xls]Sheet1!$X$2:$X$5000,{"2021","2022","2023","2025"}, 0))) be simplified to this... --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) (since it would occur inside a SUMPRODUCT function)? Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? Rick |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
It is also better to use the built in comma delimited way of SUMPRODUCT
when possible since it ignores text in the summarize range, Rick's formula will return value errors if there is for instance blanks derived from "" or any other text value in Main!$K$2:$K$5000. While there might not be many direct text entries in a range that needs to be summarized it is not far fetched to expect it can contain formulas like =IF(ISNA(VLOOKUP),"",VLOOKUP) or IF(X="","",X) -- Regards, Peo Sjoblom "T. Valko" wrote in message ... Yes, the multiplication form does work but the ISNUMBER(MATCH(...)) method is better. I haven't done a calc time test, though. In the (...)*(...) form you're evaluating each element (cell_ref) of the array against *all* 4 variables. This returns 4 intermediate results for each element. The ISNUMBER(MATCH(...)) method evaluates each element of the array against *any* of the variables and returns only a single intermediate result for each element. So, logic tells me that this should be more efficient. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Yes, I get the #VALUE! error if I use your comma-separated form of the SUMPRODUCT formula; however, if you use the multiplication form of it (which is my personal preference), this formula... =SUMPRODUCT((Main!$F$2:$F$5000=A14)*(Main!$F$2:$F $5000<=B14)*(Main!$X$2:$X$5000={2021,2022,2023,202 5})*Main!$K$2:$K$5000) which uses the change I suggested, appears to produce the exact same answers as the formula you posted; although I am still just a little too newly returned to Excel after my long absences from it to understand why. OT: Be on the look-out for the email I just sent to you. Rick "T. Valko" wrote in message ... Can't the 3rd term inside your SUMPRODUCT ... be simplified to this... --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) No. The logic of ISNUMBER(MATCH(...)) is OR(cell_ref={"2021","2022","2023","2025"}) --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) Is the same as AND(cell_ref={"2021","2022","2023","2025"}) Since a single cell won't contain all of the variables you'll end up with a #VALUE! error because the evaluated arrays are not the same size. Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? No, I didn't. What address did you use? biffinpitt is a bogus address. A good address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Those values are in fact text If I was to just add the "" around them within your formula it will still work, correct? Yes: =SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{"2021","2022"," 2023","2025"},0))),Main!$K$2:$K$5000) Can't the 3rd term inside your SUMPRODUCT, which is this... --(ISNUMBER(MATCH([Main.xls]Sheet1!$X$2:$X$5000,{"2021","2022","2023","2025"}, 0))) be simplified to this... --([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"}) (since it would occur inside a SUMPRODUCT function)? Also, on an "off topic" note... Did you receive the private email I sent you on Sept 2nd? Rick |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Yes, the multiplication form does work but the ISNUMBER(MATCH(...)) method
is better. I haven't done a calc time test, though. In the (...)*(...) form you're evaluating each element (cell_ref) of the array against *all* 4 variables. This returns 4 intermediate results for each element. The ISNUMBER(MATCH(...)) method evaluates each element of the array against *any* of the variables and returns only a single intermediate result for each element. So, logic tells me that this should be more efficient. Peo's comments (which make for a compelling argument for "knowing" your data before constructing formulas to analyze it) notwithstanding, I would be interested in seeing such a time test. Although I understand your logic, my experience with other languages tells me that straight comparisons are almost always much faster than calls into a function plus that function's code execution (and here, you are executing two separate function calls); plus, one would think that underneath the MATCH function call is a series of comparison operations not too dissimilar to those being done in the modification I proposed to your formula (although true, the MATCH function's code would be at compiled code speed whereas the formula comparisons would be at interpreted code speed). I'm thinking that in the end, more than likely, you are probably right, but seeing a time test would be interesting. Rick |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
"Rick Rothstein (MVP - VB)" wrote in
message ... Yes, the multiplication form does work but the ISNUMBER(MATCH(...)) method is better. I haven't done a calc time test, though. In the (...)*(...) form you're evaluating each element (cell_ref) of the array against *all* 4 variables. This returns 4 intermediate results for each element. The ISNUMBER(MATCH(...)) method evaluates each element of the array against *any* of the variables and returns only a single intermediate result for each element. So, logic tells me that this should be more efficient. Peo's comments (which make for a compelling argument for "knowing" your data before constructing formulas to analyze it) notwithstanding, I would be interested in seeing such a time test. Although I understand your logic, my experience with other languages tells me that straight comparisons are almost always much faster than calls into a function plus that function's code execution (and here, you are executing two separate function calls); plus, one would think that underneath the MATCH function call is a series of comparison operations not too dissimilar to those being done in the modification I proposed to your formula (although true, the MATCH function's code would be at compiled code speed whereas the formula comparisons would be at interpreted code speed). I'm thinking that in the end, more than likely, you are probably right, but seeing a time test would be interesting. Rick Calc times screencap: http://img110.imageshack.us/img110/5...lctimeshm8.jpg As you can see the the larger the range the more "significant" the difference. Calc timer code (thanks to Charles Williams): http://msdn2.microsoft.com/en-us/library/aa730921.aspx -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
Sumproduct Help | Excel Worksheet Functions | |||
HELP!!! On SumProduct | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions |