From: Tony on

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
From: Brad on
=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
From: Brad on
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
From: T. Valko on
>=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" <Tony(a)discussions.microsoft.com> wrote in message
news:6CDB4BAB-7CB6-4439-92C6-AB503FBEC9E7(a)microsoft.com...
>
> 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


From: Jacob Skaria on
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