Replace SUMIF with SUMPRODUCT

[torooo]

I am trying to replace the formula listed below with SUMPRODUCT:

=SUMIF('M:\M Inc\F2007\Revenue, Volume and Expenses By Client
\Calculation Templates\[TD F2007 January Revenue and Volume
Statistics.xls]Sheet1'!$E$164:$E$171,$A8,'M:\M Inc\F2007\Revenue,
Volume and Expenses By Client\Calculation Templates\[TD F2007 January
Revenue and Volume Statistics.xls]Sheet1'!$F$164:$F$171)+SUMIF('M:\M
Inc\F2007\Revenue, Volume and Expenses By Client\Calculation Templates\
[TD F2007 January Revenue and Volume Statistics.xls]Sheet1'!$E$130:$E
$156,$A8,'M:\M Inc\F2007\Revenue, Volume and Expenses By Client
\Calculation Templates\[TD F2007 January Revenue and Volume
Statistics.xls]Sheet1'!$F$130:$F$156)

Replacing the sumif with =SUMPRODUCT(- gives me th #value error.

Thanks in advance for your assitance.

 

[Peo Sjoblom]

1.

Open the source book, that way we don't have to deal with all these long
path text strings.

2.

Post the formula that returns value error



=SUMPRODUCT(--($E$164:$E$171=$A8),$F$164:$F$171)

is the equivalent of the first part of your SUMIF minus path and sheet name,
add the path and sheet name yourself, then you can use the same for the
other parts of the formula after changing the cell references etc. Note that
if you have the source book open when you edit the formulas you don't need
to work with all this extra text


--


Regards,


Peo Sjoblom

 

[torooo]

Thanks Peo for responding...

Here is the short version of the formula

=SUMIF('Sheet1'!$E$164:$E$171,$A9,'Sheet1'!$F$164:$F
$171)+SUMIF('Sheet1'!$E$130:$E$156,$A9,'Sheet1'!$F$130:$F$156)

Regards

 

[EricBB]

=SUMPRODUCT(--('Sheet1'!$E$164:$E$171=$A9),'Sheet1'!$F$164:$F
$171)+SUMPRODUCT(--('Sheet1'!$E$130:$E$156=$A9),'Sheet1'!$F$130:$F$156)