Sumproduct.................

[Kstalker]

I have a problem surrounding sumproduct and setting criteria.

I need to sumproduct based on row criteria '19:19'. The value for the row criteria is set in an other cell 'E23'.

So sum of row 9 and row 17 where 19 is equal to E23.


=SUMPRODUCT((OFFSET(A9,,MATCH(E23,19:19,0)-1,(OFFSET(A17,,MATCH(E23,19:19,0)+1)/1))))


Any help greatly appreciated

[Biff]

Hi!

Try this:

=SUM(OFFSET(A9,,MATCH(E23,19:19,0)-1),OFFSET(A17,,MATCH(E23,19:19,0)-1))

Biff

"Kstalker" <Kstalker.1v2myf_1126235125.0089@excelforum-nospam.com> wrote in
message news:Kstalker.1v2myf_1126235125.0089@excelforum-nospam.com...
>
> I have a problem surrounding sumproduct and setting criteria.
>
> I need to sumproduct based on row criteria '19:19'. The value for the
> row criteria is set in an other cell 'E23'.
>
> So sum of row 9 and row 17 where 19 is equal to E23.
>
>
> =SUMPRODUCT((OFFSET(A9,,MATCH(E23,19:19,0)-1,(OFFSET(A17,,MATCH(E23,19:19,0)+1)/1))))
>
>
> Any help greatly appreciated
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
> http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=466133
>

[Kstalker]

Cheers Biff.

I do not appear to be getting the correct answer using this. I think that it is not picking up all the instances that match the criteria in E23..... So I am not getting the correct answer...

Kristan

[Arvi Laanemets]

Hi

SUMPRODUCT doesn't work with entire rows or columns - you must have all
ranges to be determined, or even better - use dynamic named ranges. NB! in
SUMPRODUCT, all ranges MUST be of same dimension.


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Kstalker" <Kstalker.1v2myf_1126235125.0089@excelforum-nospam.com> wrote in
message news:Kstalker.1v2myf_1126235125.0089@excelforum-nospam.com...
>
> I have a problem surrounding sumproduct and setting criteria.
>
> I need to sumproduct based on row criteria '19:19'. The value for the
> row criteria is set in an other cell 'E23'.
>
> So sum of row 9 and row 17 where 19 is equal to E23.
>
>
> =SUMPRODUCT((OFFSET(A9,,MATCH(E23,19:19,0)-1,(OFFSET(A17,,MATCH(E23,19:19,0)+1)/1))))
>
>
> Any help greatly appreciated
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
> http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=466133
>

[Biff]

Hi!

Perhaps a better explanation of what you are trying to do is needed. Try to
be VERY SPECIFIC.

What is the value in E23?

Biff

"Kstalker" <Kstalker.1v2sig_1126242329.3548@excelforum-nospam.com> wrote in
message news:Kstalker.1v2sig_1126242329.3548@excelforum-nospam.com...
>
> Cheers Biff.
>
> I do not appear to be getting the correct answer using this. I think
> that it is not picking up all the instances that match the criteria in
> E23..... So I am not getting the correct answer...
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
> http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=466133
>

[Biff]

>SUMPRODUCT doesn't work with entire rows

=SUMPRODUCT(--(2:2="X"),1:1)

Of course, Sumif would be a better choice.

Biff

"Arvi Laanemets" <garbage@hot.ee> wrote in message
news:OL$9ZxPtFHA.1172@TK2MSFTNGP11.phx.gbl...
> Hi
>
> SUMPRODUCT doesn't work with entire rows or columns - you must have all
> ranges to be determined, or even better - use dynamic named ranges. NB! in
> SUMPRODUCT, all ranges MUST be of same dimension.
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvil<at>tarkon.ee )
>
>
>
> "Kstalker" <Kstalker.1v2myf_1126235125.0089@excelforum-nospam.com> wrote
> in message news:Kstalker.1v2myf_1126235125.0089@excelforum-nospam.com...
>>
>> I have a problem surrounding sumproduct and setting criteria.
>>
>> I need to sumproduct based on row criteria '19:19'. The value for the
>> row criteria is set in an other cell 'E23'.
>>
>> So sum of row 9 and row 17 where 19 is equal to E23.
>>
>>
>> =SUMPRODUCT((OFFSET(A9,,MATCH(E23,19:19,0)-1,(OFFSET(A17,,MATCH(E23,19:19,0)+1)/1))))
>>
>>
>> Any help greatly appreciated
>>
>>
>> --
>> Kstalker
>> ------------------------------------------------------------------------
>> Kstalker's Profile:
>> http://www.excelforum.com/member.php...o&userid=24699
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=466133
>>
>
>

[Kstalker]

Biff.

Basically the 'B9' reference sets the condition that needs to be met in row 6. So where row 6 = cell B9 then I need to sumproduct rows 2 and 4. So the answer I believe should be 7*9+8*8+9*7 = 190. However I cannot get this to work based on setting criteria.


A B C D E F
1 12 2 5 6 5 9
2 2 5 7 8 9 11
3 8 8 5 4 7 6
4 3 2 9 8 7 5
5
6 0 0 2 2 2 0
7
8
9 2


Thanks

Kristan

[Domenic]

Try...

=SUMPRODUCT(--(A6:F6=H1),A2:F2,A4:F4)

....where H1 contains your criterion.

Hope this helps!

In article <Kstalker.1v7t2d_1126476326.1962@excelforum-nospam.com>,
Kstalker <Kstalker.1v7t2d_1126476326.1962@excelforum-nospam.com>
wrote:

> Biff.
>
> Basically the 'B9' reference sets the condition that needs to be met in
> row 6. So where row 6 = cell B9 then I need to sumproduct rows 2 and 4.
> So the answer I believe should be 7*9+8*8+9*7 = 190. However I cannot
> get this to work based on setting criteria.
>
>
> A B C D E F
> 1 12 2 5 6 5 9
> 2 2 5 7 8 9 11
> 3 8 8 5 4 7 6
> 4 3 2 9 8 7 5
> 5
> 6 0 0 2 2 2 0
> 7
> 8
> 9 2
>
>
> Thanks
>
> Kristan

[Kstalker]

Excellent, works perfectly thanks Domenic.

Thanks for the input Biff.

Regards

Kristan