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

Hi!

Try this:

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

Biff

"Kstalker" wrote in
message ...

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

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

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<attarkon.ee )



"Kstalker" wrote in
message ...

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

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" wrote in
message ...

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

SUMPRODUCT doesn't work with entire rows

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

Of course, Sumif would be a better choice.

Biff

"Arvi Laanemets" wrote in message
...
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<attarkon.ee )



"Kstalker" wrote
in message ...

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.

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
------------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=466133

Try...

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

....where H1 contains your criterion.

Hope this helps!

In article ,
Kstalker
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

Excellent, works perfectly thanks Domenic.

Thanks for the input Biff.

Regards

Kristan


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=466133