SUMPRODUCT
It looks like you've omitted the following condition from the second
SUMPRODUCT function...
--(TCS!$E$2:$E$1000="GDS")
Also, consider simply using...
=SUMPRODUCT(--(TCS!$E$2:$E$1000="GDS"),--(TCS!$Z$2:$Z$1000=H2),--(TCS!$AA
$2:$AA$1000<""))
....and custom formatting the cell to hide a zero value...
Format Cells Number Custom Type: [=0]""
Note that the underlying value is still 0.
Hope this helps!
In article <62e3353d1333f@uwe, "s2m via OfficeKB.com" <u23063@uwe
wrote:
The number that is calculated is correct if I do not use (--(TCS!$E$2:$E
$1000="GDS"). Do I have the brackets in the right place?
=IF(SUMPRODUCT(--(TCS!$E$2:$E$1000="GDS"),--(TCS!$Z$2:$Z$1000=H2),--(TCS!$AA
$2:$AA$1000<""))<0,SUMPRODUCT(--(TCS!$Z$2:$Z$1000=H2),--(TCS!$AA$2:$AA
$1000<"")),"")
Thanks
Sharon
|