Variable Sumproduct Range

T

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

Brad

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

Caps not important
 
T

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top