I have a simple 8 column list of transactions with one of the columns being value and two others being marker columns, ie one indicates a T against transactions and the other an N. A transaction can be both T & N.
I can run a SUMIF to see the value of all my T transactions and another to see the value of all my N transactions but for the life of me & my hair I can't run a SUMIF to see the value of all the N transactions that are also T transactions.
Any help would be much appreciated by me, my wife, colleagues and the cat.
Thanks
Paul Scholes
Replies (7)
Please login or register to join the discussion.
Multiple criteria on sumif
Paul
You can us the following array formulae - in this example col.A contains the values, col.B the "t" codes, and col.C the "n" codes.
This needs to be created as an array formule - i.e. rather than just pressing enter, press Ctrl, Shift and Enter
=SUM((B1:B5="t")*(C1:C5="n")*A1:A5)
Hope this saves your hair and your life!
Mike
What about DSUM?
You could use the DSUM command - it means setting up a criteria table and using column headers but can be more flexible - you don't have to muck about wih the formula to change the criteria. The function wizard and help are quite good on explaining it(better than I am)
Fraser
Just make sure
ValueRange, TRange, and NRange are all the same size - though if you have managed SUMIF you will probably be aware of that already.
I would suggest you look at www.cpearson.com/excel.htm but the site seems to be down at present. If/when it comes back there is a good page that covers multiple AND as well as OR SUMIF's using this method
You can use SUMPRODUCT to run multiple IF's for a SUMIF
Another way of setting the formula would be
SUMPRODUCT((TRange="T")*(NRange="N")*ValueRange)
What it is doing is working its way down each row of each of your array ranges and saying does
T1 = T, True/Yes = 1 False/No = 0
N1 = N, True/Yes = 1 False/No = 0
multiply the answers for T1 & N1 and multiply Value 1 by that then move on to T2, N2 and Value 2 and sum the results of each row until all rows in each array range have been tested
So if either or both of the T & N tests on a row is false Excel will multiply value for the row by 0 and get the answer 0 to add to the overall total, because anything multiplied by 0 = 0, but if T & N are both True you will have 1 x 1 = 1 x value for the row.
Does that make sense?
Paul
Good to know you have it sorted.
Clint's suggestion & my expansion [which probably put you off ;)] do exactly the same as the option you chose, but without the need for the {} brackets - which you get by closing the formula edit with Ctrl + Shift + Enter
Just change it from
=SUMPRODUCT(ValueRange,TRange="T",NRange="N")
to
=SUMPRODUCT(A1:A5,B1:B5="T",C1:C5="N")
Concatenate
Add another column then use "concatenate" on the original two columns. The positive result will show TN in the new column, then do your "sumif" on the new column looking for TN.