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