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

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

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

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

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

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

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