|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 13th, 2010, 02:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Sumproduct Range
Hoping someone far smarter than myself can help. The below formula will give a result where column A=Monday, col B=John and sum the corresponding values in column C. =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) But what if I need the (C5:C10) part to be variable from anywhere between columns C to G, and determined by a value entered into say cell A1. Meaning if the number 1 was typed into that cell the formula would be as above: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) or if the value typed into A1 was 2 then the formula would effectively be: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(D5  10))I was trying to find a way to make the variable part which is dependent on cell A1 reference the numbers in row 1 as per the below so that the result retuned would be equivalent to: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(E5:E10)) A B C D E F G 1 3 1 2 3 4 5 2 3 4 5 Monday john 23 43 37 31 25 6 Saturday tony 33 32 26 20 14 7 Friday john 54 76 70 64 58 8 Monday tony 56 46 40 34 28 9 Friday anth 53 43 37 31 25 10 Monday john 23 45 39 33 27 Any help would be fantastic. Thanks Tony 
|
|
#2
May 13th, 2010, 02:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable Sumproduct Range
=SUMPRODUCT(--(A5:A10="Monday"),--(B5:B10="John"),INDIRECT(A1&5&":"&A1&10))
-- Wag more, bark less "Tony" wrote: Hoping someone far smarter than myself can help. The below formula will give a result where column A=Monday, col B=John and sum the corresponding values in column C. =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) But what if I need the (C5:C10) part to be variable from anywhere between columns C to G, and determined by a value entered into say cell A1. Meaning if the number 1 was typed into that cell the formula would be as above: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) or if the value typed into A1 was 2 then the formula would effectively be: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(D5 10))I was trying to find a way to make the variable part which is dependent on cell A1 reference the numbers in row 1 as per the below so that the result retuned would be equivalent to: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(E5:E10)) A B C D E F G 1 3 1 2 3 4 5 2 3 4 5 Monday john 23 43 37 31 25 6 Saturday tony 33 32 26 20 14 7 Friday john 54 76 70 64 58 8 Monday tony 56 46 40 34 28 9 Friday anth 53 43 37 31 25 10 Monday john 23 45 39 33 27 Any help would be fantastic. Thanks Tony
|
|
#3
May 13th, 2010, 02:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable Sumproduct Range
My formula assumes that you will put in a C, or D, or E ... in cell A1
Caps not important -- Wag more, bark less "Brad" wrote: =SUMPRODUCT(--(A5:A10="Monday"),--(B5:B10="John"),INDIRECT(A1&5&":"&A1&10)) -- Wag more, bark less "Tony" wrote: Hoping someone far smarter than myself can help. The below formula will give a result where column A=Monday, col B=John and sum the corresponding values in column C. =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) But what if I need the (C5:C10) part to be variable from anywhere between columns C to G, and determined by a value entered into say cell A1. Meaning if the number 1 was typed into that cell the formula would be as above: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) or if the value typed into A1 was 2 then the formula would effectively be: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(D5 10))I was trying to find a way to make the variable part which is dependent on cell A1 reference the numbers in row 1 as per the below so that the result retuned would be equivalent to: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(E5:E10)) A B C D E F G 1 3 1 2 3 4 5 2 3 4 5 Monday john 23 43 37 31 25 6 Saturday tony 33 32 26 20 14 7 Friday john 54 76 70 64 58 8 Monday tony 56 46 40 34 28 9 Friday anth 53 43 37 31 25 10 Monday john 23 45 39 33 27 Any help would be fantastic. Thanks Tony
|
|
#4
May 13th, 2010, 03:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable Sumproduct Range
=sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10))
I need the (C5:C10) part to be variable from anywhere between columns C to G and determined by a value entered into say cell A1. Try this... =SUMPRODUCT(--(A5:A10="Monday"),--(B5:B10="John"),INDEX(C5:G10,,A1)) -- Biff Microsoft Excel MVP "Tony" wrote in message ... Hoping someone far smarter than myself can help. The below formula will give a result where column A=Monday, col B=John and sum the corresponding values in column C. =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) But what if I need the (C5:C10) part to be variable from anywhere between columns C to G, and determined by a value entered into say cell A1. Meaning if the number 1 was typed into that cell the formula would be as above: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) or if the value typed into A1 was 2 then the formula would effectively be: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(D5 10))I was trying to find a way to make the variable part which is dependent on cell A1 reference the numbers in row 1 as per the below so that the result retuned would be equivalent to: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(E5:E10)) A B C D E F G 1 3 1 2 3 4 5 2 3 4 5 Monday john 23 43 37 31 25 6 Saturday tony 33 32 26 20 14 7 Friday john 54 76 70 64 58 8 Monday tony 56 46 40 34 28 9 Friday anth 53 43 37 31 25 10 Monday john 23 45 39 33 27 Any help would be fantastic. Thanks Tony
|
|
#5
May 13th, 2010, 04:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable Sumproduct Range
Try
=SUMPRODUCT((A5:A10="Monday")*(B5:B10="John")*(C1: G1=A1)*(C5:G10)) -- Jacob (MVP - Excel) "Brad" wrote: =SUMPRODUCT(--(A5:A10="Monday"),--(B5:B10="John"),INDIRECT(A1&5&":"&A1&10)) -- Wag more, bark less "Tony" wrote: Hoping someone far smarter than myself can help. The below formula will give a result where column A=Monday, col B=John and sum the corresponding values in column C. =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) But what if I need the (C5:C10) part to be variable from anywhere between columns C to G, and determined by a value entered into say cell A1. Meaning if the number 1 was typed into that cell the formula would be as above: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) or if the value typed into A1 was 2 then the formula would effectively be: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(D5 10))I was trying to find a way to make the variable part which is dependent on cell A1 reference the numbers in row 1 as per the below so that the result retuned would be equivalent to: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(E5:E10)) A B C D E F G 1 3 1 2 3 4 5 2 3 4 5 Monday john 23 43 37 31 25 6 Saturday tony 33 32 26 20 14 7 Friday john 54 76 70 64 58 8 Monday tony 56 46 40 34 28 9 Friday anth 53 43 37 31 25 10 Monday john 23 45 39 33 27 Any help would be fantastic. Thanks Tony
|
|
#6
May 14th, 2010, 02:11 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable Sumproduct Range
Thank you Brad, Jacob and T.Valko for all your responses, it all works and
does exactly what I needed. Thanks for your help. "Tony" wrote: Hoping someone far smarter than myself can help. The below formula will give a result where column A=Monday, col B=John and sum the corresponding values in column C. =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) But what if I need the (C5:C10) part to be variable from anywhere between columns C to G, and determined by a value entered into say cell A1. Meaning if the number 1 was typed into that cell the formula would be as above: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) or if the value typed into A1 was 2 then the formula would effectively be: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(D5 10))I was trying to find a way to make the variable part which is dependent on cell A1 reference the numbers in row 1 as per the below so that the result retuned would be equivalent to: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(E5:E10)) A B C D E F G 1 3 1 2 3 4 5 2 3 4 5 Monday john 23 43 37 31 25 6 Saturday tony 33 32 26 20 14 7 Friday john 54 76 70 64 58 8 Monday tony 56 46 40 34 28 9 Friday anth 53 43 37 31 25 10 Monday john 23 45 39 33 27 Any help would be fantastic. Thanks Tony
|
|
#7
May 14th, 2010, 02:24 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable Sumproduct Range
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tony" wrote in message ... Thank you Brad, Jacob and T.Valko for all your responses, it all works and does exactly what I needed. Thanks for your help. "Tony" wrote: Hoping someone far smarter than myself can help. The below formula will give a result where column A=Monday, col B=John and sum the corresponding values in column C. =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) But what if I need the (C5:C10) part to be variable from anywhere between columns C to G, and determined by a value entered into say cell A1. Meaning if the number 1 was typed into that cell the formula would be as above: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) or if the value typed into A1 was 2 then the formula would effectively be: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(D5 10))I was trying to find a way to make the variable part which is dependent on cell A1 reference the numbers in row 1 as per the below so that the result retuned would be equivalent to: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(E5:E10)) A B C D E F G 1 3 1 2 3 4 5 2 3 4 5 Monday john 23 43 37 31 25 6 Saturday tony 33 32 26 20 14 7 Friday john 54 76 70 64 58 8 Monday tony 56 46 40 34 28 9 Friday anth 53 43 37 31 25 10 Monday john 23 45 39 33 27 Any help would be fantastic. Thanks Tony
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
