Joining a sumproduct function

[sbrowner]

My formulas work as a stand alone but I need to have a sumproduct formula to pickup Branch 1 and Branch 2 and total them in the same column.

Current formula in two separate columns:

=SUMPRODUCT((BRANCH07="01")*(DEPT07="20")*(CATEGORY_DETAIL07=$A10)*(JUN_07_YTD))*-1

=SUMPRODUCT((BRANCH07="02")*(DEPT07="20")*(CATEGORY_DETAIL07=$A10)*(JUN_07_YTD))*-1

I thought it would be so simple to join them but I clearly am missing something because I keep getting errors.

Thanks

 

[Scott Huish]

This should really be in the Excel questions forum, but:
=-SUMPRODUCT(--((BRANCH07="01")+(BRANCH07="20")>0),--(DEPT07="20"),--(CATEGORY_DETAIL07=$A10),JUN_07_YTD)

 

[pgc01]

Hi

A similar one:

=-SUMPRODUCT((BRANCH07="01")+(BRANCH07="20"),--(DEPT07="20"),--(CATEGORY_DETAIL07=$A10),JUN_07_YTD)

 

[shg]

Or

=-SUMPRODUCT((BRANCH07={"01","20"})*(DEPT07="20")*(CATEGORY_DETAIL07=$A10),JUN_07_YTD)

EDIT: Looking at that, I expect the two other alternatives are faster.