Solved: sum specific data

[Klartigue]

In column A of a spreadsheet, there is a list of brokers. In column C, there is either "buy" or "sell". And in column J, there are quantities. What is an excel function to use if I am trying to find the total sum of sales for each broker. This seems easy, but at the end of everyday i add more transactions to this list and want the total sum to update automatically. The brokers are not sorted or separated in any particular way so i need an excel function that can identify the same broker (which will appear in many different rows), identifiy that it is sell in column c, and therefore sum the quantities in column j.. Is there any sort of exccel function that can do this?

[Aflatoon]

Either a pivot table or SUMPRODUCT if you have a list of the brokers.
=SUMPRODUCT(--(A1:A100="broker name"),--(C1:C100="buy"),J1:J100)
for example.
Also, if using 2007 or later, you can use SUMIFS:
=SUMPRODUCT(J1:J100,A1:A100,"broker name",C1:C100,"buy")

[Klartigue]

Ok this is what I tried but its not working..hmm.

I am trying to sum the quantities for sells for broker Advisor Asset Managment ...

=SUMPRODUCT(Trades!J:J,Trades!A:A,"Advisor Asset Management", Trades!C:C,"sell")


Do you see something wrong with my equation?

[Aflatoon]

Yes - that is not what I posted - it's a cross between the two. Please recheck the SUMPRODUCT syntax.
Also, if you are using 2003 or earlier, you cannot refer to entire columns - you must specify a range.

[Klartigue]

=SUMIFS(J1:J10000, A2:A10000, "=Advisor Asset Management", C2:C10000, "=sell")

Ok i am working in Excel 2010 and decided to use the SUMIF function.

Adds the cells in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula:
=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")I am trying to follow these instructions but SUMIFS function doesnt seem to be working..

[Aflatoon]

Not working in what way? That formula is correct in terms of syntax.

Did you try a pivot table, incidentally?

[Klartigue]

Actually I just used a pivot table.. that was much easier. Thanks for the suggestion!