SUMPRODUCT not equal to...
 

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?


--
Brigitte
------------------------------------------------------------------------
Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782
View this thread: http://www.excelforum.com/showthread...hreadid=564371

SUMPRODUCT not equal to...
 

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
..
"Brigitte" wrote in
message ...

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?


--
Brigitte
------------------------------------------------------------------------
Brigitte's Profile:
http://www.excelforum.com/member.php...o&userid=32782
View this thread: http://www.excelforum.com/showthread...hreadid=564371

SUMPRODUCT not equal to...
 

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

"Brigitte" wrote:


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?


--
Brigitte
------------------------------------------------------------------------
Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782
View this thread: http://www.excelforum.com/showthread...hreadid=564371