sumproduct problem

[christophe meresse]

Hi

When I write my function {=sommeprod((toto1=$C31)*(tutu1=$D31))}
I've the correct result : correct number of match in relation with my
demands in C31 and D31
toto1 is Range(E8:E78) of another sheet
tutu1 is Range(F8:F78) of another sheet

But when I write {=sommeprod((N28=$C31)*(P28=$D31))}
It doesn't work...even I've written toto1 in N28 and tutu1 in P28
What's the problem?

 

[Domenic]

Try...

=SOMMEPROD((INDIRECT($N$28)=$C31)*(INDIRECT($P$28)=$D31))

Note that the formula does not need to be confirmed with
CONTROL+SHIFT+ENTER. Confirm with just ENTER.

Hope this helps!

 

[KL]

Hi christophe,

A few things:

When I write my function {=sommeprod((toto1=$C31)*(tutu1=$D31))}
I've the correct result : correct number of match in relation with my
demands in C31 and D31
toto1 is Range(E8:E78) of another sheet
tutu1 is Range(F8:F78) of another sheet

1) You DO NOT need to array-enter your formula (Ctrl+Shift+Enter) as
SOMMEPROD() can handle arrays anyway.
2) In this case the defined names "toto1" and "tutu1" in your formula return
the underlying ranges/arrays [E8:E78] and [F8:F78]

But when I write {=sommeprod((N28=$C31)*(P28=$D31))}
It doesn't work...even I've written toto1 in N28 and tutu1 in P28

3) In this case the references N28 and P28 return their respective values
which are text strings "toto1" and "tutu1". Thus what your formula does is
compare the text "toto1" against the value of C31 and the text "tutu1"
against the value of D31.
4) Try this formula instead:

=SOMMEPROD((INDIRECT(N28)=$C31)*(INDIRECT(P28)=$D31))

Regards,
KL

 

[christophe meresse]

Thank you for your help, it works very good !!