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