Thread: 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?

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" <Brigitte.2bgl5j_1153760220.6567@excelforum-nospam.com> wrote in
message news:Brigitte.2bgl5j_1153760220.6567@excelforum-nospam.com...
>
> 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(--($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
>
>