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

>SUMPRODUCT doesn't work with entire rows

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

Of course, Sumif would be a better choice.

Biff

"Arvi Laanemets" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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

Try...

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

....where H1 contains your criterion.

Hope this helps!

In article <[email protected]>,
Kstalker <[email protected]>
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