SUMPRODUCT
 

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

--
Message posted via http://www.officekb.com

SUMPRODUCT
 

It looks OK.
could the "GDS" cells have trailing or leading spaces ?
try

=IF(SUMPRODUCT(--(Trim(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<"")),"")



"s2m via OfficeKB.com" 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

--
Message posted via http://www.officekb.com

SUMPRODUCT
 

No I checked that, any other ideas?

bj wrote:
It looks OK.
could the "GDS" cells have trailing or leading spaces ?
try

=IF(SUMPRODUCT(--(Trim(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<"")),"")

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?
[quoted text clipped - 6 lines]

Sharon

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

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

SUMPRODUCT
 

Excellent!! It works

Thanks so much

Domenic wrote:
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!

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?
[quoted text clipped - 6 lines]

Sharon

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1