Sumproduct

[T De Villiers]

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$381=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 Govier]

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"))*(DUMP!$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]

Roger,

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