SUMPRODUCT not equal to...

[Brigitte]

I have the following data set:

A1 B1 C1
ControlTotals Account Amount
Development 100001 $50
Communications 100002 $70
Communications 100001 $75
Operations 100001 $1,115
Control Total 100001 $101,530

And the following formula:
=SUMPRODUCT(--($A$2:$A$1000="Control
Total"),--($B$2:$B$1000="100001"),C$2:C$1000)

Is there a way to manipulate this formula to sum all of the 100001
Amounts and NOT the amounts with "Control Total" in column A?

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--($A$2:$A$1000<>"Control
Total"),--($B$2:$B$1000="100001"),C$2:C$1000)

The <> operator means "not equal to"

Biff
..

 

[Guest]

=SUMPRODUCT(--($A$2:$A$6<>"Control Total"),--($B$2:$B$6="100001"),C$2:C$6)