|
Entered data for use in formula
I am trying to make formulae that is used regularly be more user friendly.
Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
Hi Mark
Try =SUMPRODUCT((INDIRECT(A1):INDIRECT(A2)<1000)* INDIRECT(B1):INDIRECT(B2)) -- Regards Roger Govier "Mark11" wrote in message ... I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
=SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2)))
Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g. if you enter A2=50 and B2=51 then you get error! Regards, Stefi €˛Mark11€¯ ezt Ć*rta: I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
If I were you I'd structure entered data like this:
A1: condition column - D B1: sum column - E A2: row from - 3 B2: row to - 50 In this case the formula: =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) Regards, Stefi =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) €˛Stefi€¯ ezt Ć*rta: =SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2))) Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g. if you enter A2=50 and B2=51 then you get error! Regards, Stefi €˛Mark11€¯ ezt Ć*rta: I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
Thanks Roger and Stefi. Both those solutions worked perfectly.
Cheers. "Stefi" wrote: If I were you I'd structure entered data like this: A1: condition column - D B1: sum column - E A2: row from - 3 B2: row to - 50 In this case the formula: =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) Regards, Stefi =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) €˛Stefi€¯ ezt Ć*rta: =SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2))) Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g. if you enter A2=50 and B2=51 then you get error! Regards, Stefi €˛Mark11€¯ ezt Ć*rta: I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
Actually, it worked for simple sumproduct formulae, but once I tried
=SUMPRODUCT(--(ISNUMBER(FIND($B$8,(indirect($B$3&$B$6):indirect( $B$3&$B$7)))),--(indirect($B$5&$B$6):indirect($B$5&$B$7)<$f4))) Where B8 is text, B3=AB, B5=AH, B6=2, B7=207 and f4=1000 It was supposed to look to AB2:AB207 and find, say, "DIESEL" (f4) and sum all totals of diesel orders in (AH2:AH207) under 1000. It just keeps giving #VALUE and says "All arrays must have the same dimensions" Clearly I don't know what this means, nor how to fix it. Any ideas? "Mark11" wrote: Thanks Roger and Stefi. Both those solutions worked perfectly. Cheers. "Stefi" wrote: If I were you I'd structure entered data like this: A1: condition column - D B1: sum column - E A2: row from - 3 B2: row to - 50 In this case the formula: =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) Regards, Stefi =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) €˛Stefi€¯ ezt Ć*rta: =SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2))) Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g. if you enter A2=50 and B2=51 then you get error! Regards, Stefi €˛Mark11€¯ ezt Ć*rta: I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
Hi Mark
Wrong number of parentheses in your formula, it should be =SUMPRODUCT(--(ISNUMBER(FIND($B$8,(INDIRECT($B$3&$B$6) :INDIRECT($B$3&$B$7))))),--(INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7)<$F4)) But that will only give you the Count of the number of Items where Diesel is less than 1000. If you want the SUM, you need to add the second range AH2:AH207 again. =SUMPRODUCT(--(ISNUMBER(FIND($B$8,(INDIRECT($B$3&$B$6) :INDIRECT($B$3&$B$7))))),--(INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7)<$F4), (INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7))) -- Regards Roger Govier "Mark11" wrote in message ... Actually, it worked for simple sumproduct formulae, but once I tried =SUMPRODUCT(--(ISNUMBER(FIND($B$8,(indirect($B$3&$B$6):indirect( $B$3&$B$7)))),--(indirect($B$5&$B$6):indirect($B$5&$B$7)<$f4))) Where B8 is text, B3=AB, B5=AH, B6=2, B7=207 and f4=1000 It was supposed to look to AB2:AB207 and find, say, "DIESEL" (f4) and sum all totals of diesel orders in (AH2:AH207) under 1000. It just keeps giving #VALUE and says "All arrays must have the same dimensions" Clearly I don't know what this means, nor how to fix it. Any ideas? "Mark11" wrote: Thanks Roger and Stefi. Both those solutions worked perfectly. Cheers. "Stefi" wrote: If I were you I'd structure entered data like this: A1: condition column - D B1: sum column - E A2: row from - 3 B2: row to - 50 In this case the formula: =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) Regards, Stefi =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) €˛Stefi€¯ ezt Ć*rta: =SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2))) Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g. if you enter A2=50 and B2=51 then you get error! Regards, Stefi €˛Mark11€¯ ezt Ć*rta: I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
Your request is not very clear, but if B8 contains "DIESEL" then maybe you
need this formula: =SUMPRODUCT(--(INDIRECT(B3&B6):INDIRECT(B3&B7)=B8),--(INDIRECT(B5&B6):INDIRECT(B5&B7)<F4),INDIRECT(B5&B 6):INDIRECT(B5&B7)) Regards, Stefi €˛Mark11€¯ ezt Ć*rta: Actually, it worked for simple sumproduct formulae, but once I tried =SUMPRODUCT(--(ISNUMBER(FIND($B$8,(indirect($B$3&$B$6):indirect( $B$3&$B$7)))),--(indirect($B$5&$B$6):indirect($B$5&$B$7)<$f4))) Where B8 is text, B3=AB, B5=AH, B6=2, B7=207 and f4=1000 It was supposed to look to AB2:AB207 and find, say, "DIESEL" (f4) and sum all totals of diesel orders in (AH2:AH207) under 1000. It just keeps giving #VALUE and says "All arrays must have the same dimensions" Clearly I don't know what this means, nor how to fix it. Any ideas? "Mark11" wrote: Thanks Roger and Stefi. Both those solutions worked perfectly. Cheers. "Stefi" wrote: If I were you I'd structure entered data like this: A1: condition column - D B1: sum column - E A2: row from - 3 B2: row to - 50 In this case the formula: =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) Regards, Stefi =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) €˛Stefi€¯ ezt Ć*rta: =SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2))) Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g. if you enter A2=50 and B2=51 then you get error! Regards, Stefi €˛Mark11€¯ ezt Ć*rta: I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
Thanks again Roger & Stefi.
Roger's solution worked! Stefi's didn't seem to - not sure why. Maybe because "Diesel" was being found in a phrase. I agree I wasn't clear, sorry. Cheers. "Stefi" wrote: Your request is not very clear, but if B8 contains "DIESEL" then maybe you need this formula: =SUMPRODUCT(--(INDIRECT(B3&B6):INDIRECT(B3&B7)=B8),--(INDIRECT(B5&B6):INDIRECT(B5&B7)<F4),INDIRECT(B5&B 6):INDIRECT(B5&B7)) Regards, Stefi €˛Mark11€¯ ezt Ć*rta: Actually, it worked for simple sumproduct formulae, but once I tried =SUMPRODUCT(--(ISNUMBER(FIND($B$8,(indirect($B$3&$B$6):indirect( $B$3&$B$7)))),--(indirect($B$5&$B$6):indirect($B$5&$B$7)<$f4))) Where B8 is text, B3=AB, B5=AH, B6=2, B7=207 and f4=1000 It was supposed to look to AB2:AB207 and find, say, "DIESEL" (f4) and sum all totals of diesel orders in (AH2:AH207) under 1000. It just keeps giving #VALUE and says "All arrays must have the same dimensions" Clearly I don't know what this means, nor how to fix it. Any ideas? "Mark11" wrote: Thanks Roger and Stefi. Both those solutions worked perfectly. Cheers. "Stefi" wrote: If I were you I'd structure entered data like this: A1: condition column - D B1: sum column - E A2: row from - 3 B2: row to - 50 In this case the formula: =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) Regards, Stefi =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) €˛Stefi€¯ ezt Ć*rta: =SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2))) Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g. if you enter A2=50 and B2=51 then you get error! Regards, Stefi €˛Mark11€¯ ezt Ć*rta: I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
Entered data for use in formula
....also - Roger is right - Sorry - I pasted the wrong formula! I do count
with that one and sum with another. I blame late afternoon and formula frustration syndrome! :) "Mark11" wrote: Thanks again Roger & Stefi. Roger's solution worked! Stefi's didn't seem to - not sure why. Maybe because "Diesel" was being found in a phrase. I agree I wasn't clear, sorry. Cheers. "Stefi" wrote: Your request is not very clear, but if B8 contains "DIESEL" then maybe you need this formula: =SUMPRODUCT(--(INDIRECT(B3&B6):INDIRECT(B3&B7)=B8),--(INDIRECT(B5&B6):INDIRECT(B5&B7)<F4),INDIRECT(B5&B 6):INDIRECT(B5&B7)) Regards, Stefi €˛Mark11€¯ ezt Ć*rta: Actually, it worked for simple sumproduct formulae, but once I tried =SUMPRODUCT(--(ISNUMBER(FIND($B$8,(indirect($B$3&$B$6):indirect( $B$3&$B$7)))),--(indirect($B$5&$B$6):indirect($B$5&$B$7)<$f4))) Where B8 is text, B3=AB, B5=AH, B6=2, B7=207 and f4=1000 It was supposed to look to AB2:AB207 and find, say, "DIESEL" (f4) and sum all totals of diesel orders in (AH2:AH207) under 1000. It just keeps giving #VALUE and says "All arrays must have the same dimensions" Clearly I don't know what this means, nor how to fix it. Any ideas? "Mark11" wrote: Thanks Roger and Stefi. Both those solutions worked perfectly. Cheers. "Stefi" wrote: If I were you I'd structure entered data like this: A1: condition column - D B1: sum column - E A2: row from - 3 B2: row to - 50 In this case the formula: =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) Regards, Stefi =SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2))) €˛Stefi€¯ ezt Ć*rta: =SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2))) Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g. if you enter A2=50 and B2=51 then you get error! Regards, Stefi €˛Mark11€¯ ezt Ć*rta: I am trying to make formulae that is used regularly be more user friendly. Is there a way I can enter a range and use that input data in a formula. e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)). I have tried many variations but none seem to work. Any help would be appreciated. |
| All times are GMT +1. The time now is 09:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com