CODEWITHSUNDEEP
excelsumifs
susan
susan

Reputation: 382

Excel SUMIF column value matches

I would like to sum all values in column J when the value in column K matches. So, for example, I have the following:

COL J | COL K
25.00 | Now
45.00 | Aug 15
40.00 | Sep 10
70.00 | Now
14.00 | Aug 15
92.00 | Now

I'd like Excel to find all matching values in Column K and add up the values in corresponding rows of column J. For the example above it would sum 25.00, 70.00 and 92.00 which correspond with "Now" and then also add up 45.00 and 14.00 which correspond with Aug 15.

I know it can be done with formulas like this:
=SUMIF(K:K,"Now",J:J)
=SUMIF(K:K,"Aug 15",J:J)

However, I'd like to be more flexible and not have to have a separate formula for every different value in Column K. Is there a way to use a wildcard of some sort that can replace "Now" and "Aug 15" in the example above so that I could just have one formula that finds any matches in Column K and sums the corresponding values for those rows in Column J?

Thank you!

Upvotes: 3

Views: 16356

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15641

SUMIF accepts wildcards (e.g., this or this).

I tried adding another line to your data,

92.00 | Never

and then =SUMIF(K:K,"N*",J:J). It works great. If I understood what you were specifically aiming at, it would be =SUMIF(K:K,"*",J:J).

PS: I wouldn't know how it behaves when you have cells formatted as date, and you try to match according to what is displayed. That might involve locale issues.

Upvotes: 1

Baldy
Baldy

Reputation: 2002

You need to investigate Array Formulas also known as CSE formulas. http://www.mrexcel.com/articles/CSE-array-formulas-excel.php or http://office.microsoft.com/en-us/excel-help/guidelines-and-examples-of-array-formulas-HA010342564.aspx

=SUM(IF($K$1:$K$6=K1,$J$1:$J$6,0))

If you paste this formula into cell L1 (assuming your data is in K1:J6) and press Control-Shift-Enter to save it, it will calculate the sum of all the cells in Column J that have a matching Column K. Array Formulas are pretty powerful but they are pretty hard to debug.

Upvotes: 0

Related Questions