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

[Kstalker]

I have a problem surrounding sumproduct and setting criteria.

I need to sumproduct based on row criteria '19:19'. The value for th
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 appreciate

 

[Biff]

Hi!

Try this:

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

Biff

 

[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.

 

[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

 

[Biff]

SUMPRODUCT doesn't work with entire rows

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

Of course, Sumif would be a better choice.

Biff

 

[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!

 

[Kstalker]

Excellent, works perfectly thanks Domenic.

Thanks for the input Biff.

Regards

Krista