Sumproduct
 

In CoL C I have:
804
805
806
..
...


In Cell H1 I have the following which works fine:

=SUMPRODUCT((DUMP!$C$3:$C$381="804")*(DUMP!$F$3:$F $381=Sheet4!C$2)*(DUMP!$G$3:$G$381="Won"))

However I would like this to become:

=SUMPRODUCT((DUMP!$C$3:$C$381=Z1)*(DUMP!$F$3:$F$38 1=Sheet4!C$2)*(DUMP!$G$3:$G$381="Won"))

where Z1 contains the value 804

WHIch doesnt quite work, is this a formatting issue?


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=472950

Hi

You say you have 804, 805, 806 in Column C, but you are testing for Z1
being equal to 804. Is this the problem?

Or

Again, in your first formula which works, the 804 is within quotes "804"
so it is being treated as text.

Maybe

=SUMPRODUCT((DUMP!$C$3:$C$381=TEXT(Z1,"000"))*(DUM P!$F$3:$F$381=Sheet4!C$2)*(DUMP!$G$3:$G$381="Won") )

Are the cells on the same sheet as the formula? or do you need to say
=Sheet4!Z1?

Regards

Roger Govier



T De Villiers wrote:

In CoL C I have:
804
805
806
..
...


In Cell H1 I have the following which works fine:

=SUMPRODUCT((DUMP!$C$3:$C$381="804")*(DUMP!$F$3:$ F$381=Sheet4!C$2)*(DUMP!$G$3:$G$381="Won"))

However I would like this to become:

=SUMPRODUCT((DUMP!$C$3:$C$381=Z1)*(DUMP!$F$3:$F$3 81=Sheet4!C$2)*(DUMP!$G$3:$G$381="Won"))

where Z1 contains the value 804

WHIch doesnt quite work, is this a formatting issue?

Roger,

Text("Z1",000) worked a treat , many ths again"!


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=472950