Prev: convert matrices to table
Next: Google Adsense Account Approval Tricks
From: Tony on 13 May 2010 21:11
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
From: T. Valko on 13 May 2010 21:24
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Tony" <Tony(a)discussions.microsoft.com> wrote in message
news:608791D9-FBBC-4826-9151-D75B7352799A(a)microsoft.com...
> 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


First  |  Prev  | 
Pages: 1 2
Prev: convert matrices to table
Next: Google Adsense Account Approval Tricks