SUMIF AND SUMIF

SUMIF AND SUMIF

Didn't find your answer?

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.

avatar
By mikerees
21st Jul 2006 13:58

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

Thanks (0)
avatar
By AnonymousUser
21st Jul 2006 11:28

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

Thanks (0)
avatar
By ACDWebb
21st Jul 2006 09:13

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?

Thanks (0)
Teignmouth
By Paul Scholes
21st Jul 2006 16:40

Saved in the nick of time
Thank you all for your help. I went for Mike's as it looked easiest and despite Excel refusing to accept it for 5 minutes it suddenly came up with £17.50 which is what I've been trying for all day (think the {} each end finally did the trick).

Excel's a bit like tax & accountancy (or maybe Dark Rum)...you never know how good it is till you really get into it.

Cheers
Paul

Thanks (0)
avatar
By ACDWebb
21st Jul 2006 16:58

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")

Thanks (0)
avatar
By AnonymousUser
21st Jul 2006 08:47

Try
=SUMPRODUCT(ValueRange,TRange="T",NRange="N")

Thanks (0)
avatar
By arthurely
28th Jul 2006 15:33

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.

Thanks (0)