| Home |
| Search |
| Today's Posts |
|
#1
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:D10)) 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
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:D10)) 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
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:D10)) 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
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:D10)) 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
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:D10)) 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
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:D10)) 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
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:D10)) 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct using variable range | Excel Discussion (Misc queries) | |||
| Sumproduct with a variable range | Excel Worksheet Functions | |||
| sumproduct with one variable criteria? | Excel Worksheet Functions | |||
| SUMPRODUCT WITH A VARIABLE CRITERIA? | Excel Worksheet Functions | |||
| sumproduct - reading a variable value | Excel Discussion (Misc queries) | |||
Linear Mode
