Variable Sumproduct Range
in [Functions]
|
From: Tony on 13 May 2010 09:10 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
From: Brad on 13 May 2010 09:54 =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
From: Brad on 13 May 2010 09:58 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
From: T. Valko on 13 May 2010 10:40 >=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" <Tony(a)discussions.microsoft.com> wrote in message news:6CDB4BAB-7CB6-4439-92C6-AB503FBEC9E7(a)microsoft.com... > > 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
From: Jacob Skaria on 13 May 2010 11:16 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
|
Next
|
Last
Pages: 1 2 Prev: convert matrices to table Next: Google Adsense Account Approval Tricks |