Variable Sumproduct Range

[Tony]

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"),(D510))

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

 

[Brad]

=SUMPRODUCT(--(A5:A10="Monday"),--(B5:B10="John"),INDIRECT(A1&5&":"&A1&10))

 

[Brad]

My formula assumes that you will put in a C, or D, or E ... in cell A1

Caps not important

 

[T. Valko]

=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))

 

[Jacob Skaria]

Try

=SUMPRODUCT((A5:A10="Monday")*(B5:B10="John")*(C1:G1=A1)*(C5:G10))

 

[Tony]

Thank you Brad, Jacob and T.Valko for all your responses, it all works and
does exactly what I needed. Thanks for your help.

 

[T. Valko]

You're welcome. Thanks for the feedback!