Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT - How can I use does not equal in an array? | Excel Worksheet Functions | |||
GREATER OR EQUAL TO BUT LESS THAN Problem using Sumproduct | Excel Worksheet Functions | |||
SumProduct - Value ISN'T equal to | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |