Microsoft

A dynamic SUMIF() function based on naturally occurring data

It's tempting, but inefficient, to use literal values as the criteria in Excel's SUMIF() function. Reference the criteria in data instead, when possible.

Excel's SUMIF() function adds values that satisfy specific criteria. For instance, you might use this function to sum all the products sold by specific personnel. The sheet below shows this function returning the total of products sold by Bill, 120. The formula in E3

=SUMIF($A$2:$A$9,"Bill",$B$2:$B$9)

sums the sold values in column B when the corresponding cell in column A equals the string "Bill."

It's likely that you're familiar with this function and have even used it. The only drawback to using this function as I just did is the second argument, the criteria. As written, the criterion is static—the function will always look for the string Bill. If you only have a few criteria items to match, that's not such a big deal. On the other hand, entering a large number of functions would be a tedious and error-prone task.

Whether you need just a few or several functions, there might be an easier way--the criteria is probably in your data! By referencing the data, you can make a dynamic function  and copy it as you would a regular SUM() function. In this case, the solution is to use the criteria as sheet labels.

Instead of "hard-coding" the criteria, refer to the new label cell in column D2, which contains the string Bill. The function now refers to cell D2 instead of a literal string

=SUMIF($A$2:$A$9,$D2,$B$2:$B$9)

The final step is to copy the formula to copy the function. Creating dynamic formulas and function is basic to Excel, but you might not consider using the naturally occurring data as I've done in the SUMIF() function.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

36 comments
mpala007
mpala007

I use sumproduct() for some things when criteria is in another column. It's a good complement to sumif()

danny
danny

I have been following the comments and discussion about the relative merits of SUMIF() and a Pivot Table to summarize the data in this Excel List. I have a video on this topic (posted on YouTube for now)that shows how to do both. Here is the link: http://www.youtube.com/watch?v=CPKQ3zarzQk Danny Rocks www.thecompanyrocks.com

stapleb
stapleb

I often teach people how to use SUMIF() as I believe it is an extremely useful and simple Excel function. I do show them the "hard coding" feature so that I can show them the use of the wildcard * to cover lists where the data being used for the criteria may contain text with slightly different spelling.

Helen Phelan
Helen Phelan

I generally use a DSUM formula for this type of data summary.

rbaker_va
rbaker_va

Personally, I would do this analysis with a pivot table.

ika.turner
ika.turner

The last figure in the article shows the function incorrectly. The D2 address should not be absolute.

akhanda
akhanda

Brilliant. These elegant solutions rarely occur to anyone except in moments of serendipity!

samuel.a.dyck
samuel.a.dyck

Nice - did not know about the sumif() function, but I would have figured out the fix pretty quick.

Andrew.Istead
Andrew.Istead

Sorry am I missing something or is this a little like Excel 101?

tim
tim

Wouldn't a pivot table have provided the same result in this instance?

RRB
RRB

Stapleb, you posted a reply to my message above summing two or more sumifs to sum numbers based on two or more conditions. I couldn't do it your way. I'd appreciate if you could post an example as this subject is very interesting to me. As I said, I do it concatenating two or more columns in another and getting one criterium which was suggested by someone. TY

david.j.kennedy
david.j.kennedy

I think if you look closely, the relevant part of the of the formula is NOT absolute. Since you'd drag the formula down, while column D might be absolute, the row number is relative.

Meadowsong
Meadowsong

Only the column reference is absolute. The row reference is dynamic. As presented, that is valid.

wwgorman
wwgorman

You are correct. There are so many functions in Excel that we forget them along the way if they are not in our usual usage pattern.

Richard Gray (AUS)
Richard Gray (AUS)

What they don't really tell you in the Help is how to extend the SUMIF to use functions in the criteria element. Eg, I have a timesheet spreadsheet. I have a standard sheet that I copy for every new month. Given that some months have less thn 31 days, I need to ensure that the TOTAL cell (in row 33) only sums daily total hour values (column K) up to the last day of the month (date is in column A with first of the month occurring in A2 and allowing for up to 31 days by going up to row 32). Consequently, I use the formula: =SUMIF($A$2:$A$32,CONCATENATE("

BrucePurcell
BrucePurcell

Yes, it is Excel 101. I don't remember everything I ever learned with Excel -- I find these little reminders are often helpful. They remind me of ways to do tasks that I should know and have forgotten over time.

pkassube
pkassube

It looks that way to me too. The real win here is to automatically populate the distinct list of values in column D. Pivot Tables are so much easier.

mark.ivanowski
mark.ivanowski

SUMIF DBASE PIVOT TABLE All depends on the case. Excellent and welcome tip.

jmackeyiii
jmackeyiii

There are instances where a pivot table is to robust and to limited of a solution. a pivot is like an escalade and a sumif is like a porsche.

stapleb
stapleb

Hi RRB, I hope this is clearer. Assume I have Dog, Cat, Bird, Fish, etc in column A and numbers in Column B. The formula would read =SUMIF(A3:A10,"cat",B3:B10)+SUMIF(A3:A10,"dog",B3:B10). If I'm worried that I may have Dog, Dogs, Doggy, etc the criteria would be "dog*". Please let me know if that helps, and good luck with it. Stapleb.

sjdorst
sjdorst

And is there a "neat" way to populate column D with the unique values from column A? There's quite a few places I'd use this! Thanks in advance...

demhart
demhart

Are you putting the dollar-signs in the right places to make the $A$1:$A$10 and $B$1:$B$10 references are absolute, while $D2 is absolute for the column and relative for the row? This would also be a good place for named ranges, at least for the A and B columns...

mla_ca520
mla_ca520

Or are you having difficulty making the example work? I use them frequently and am not sure what you mean...please enlighten.

al
al

I am not an Excel person, yet I find myself having to use it from time to time. Advice like this, though considered as "101" by the experts following this blog, are just the thing for us "others". Please, keep the mix of advanced and basic information coming. We are learning! (Now I'm off to learn about "pivot tables" in Excel.)

Cavedwella
Cavedwella

Have you tried multiple criteria with an array formula? {=SUM(IF($A$1:$A$10="Critera 1",IF($B$1:$B$10="Criteria 2",$C$1:$C$10,0),0))} Entered with "Ctrl+shift+enter" Column A - List Column B - List Column C - Sum Field I can't take credit for this formula, I found it whilst searching the web for a similar problem...

stapleb
stapleb

Hi again RRB. Using the concatenate to put the two together is needed. Using the concatenated range (named CR) and the values to be added (named VR), and I'll use your text (well done thinking of Aust cities!!) I would write the formula : =SUMIF(CR,"catmelbourne",VR)+SUMIF(CR,"birdbrisbane",VR) I have used text in this example to hopefully make it clear. You could substitute the text with an absolute cell reference in the concatenated range. But, the important point is the "SUMIF(...)+SUMIF(...)" to add two criteria in the one cell. I do so hope this helps you, and doesn't leave you tearing out your hair.

RRB
RRB

Thank you Stapleb. I guess I didn't express myself correctly in my first post. Following your example, in Column C put "Cities". I would like to know home many Dogs are in Melbourne, and how many Birds in Brisbane, etc. What I did in version 2003 was to write in Column D a formula concatenating the two: =A2&C2. Then the formula would look something like SUMIF(A2:A10,"catmelbourne",D2:D10). I always use named ranges with this function as it simplifies its use. An easy way to show the results is to list the animals from A2 down in another sheet and the cities from B1 right. Then the formula in B2 would be =SUMIF(Animals,$A2&B$1,Condition), assuming that the database is in Sheet1 and the ranges are: Animals=Sheet1!$A$2:$A$5000 and Condition=Sheet1!$D$2:$D$5000. Your comments would be welcome.

danny
danny

Without question, a Pivot Table is the fastest and most accurate way to produce a list of unique values in a field of data. And, when you base your Pivot Table on an Excel "Table" the list of unique values will update automatically when you "refresh" the Pivot Table. Here is a link to a short video that I created that demonstrates SUMIF() and Pivot Table side-by-side: http://www.thecompanyrocks.com/two-ways-to-produce-an-executive-summary-of-an-excel-list/ Danny Rocks The Company Rocks

bcmrbill
bcmrbill

In excel 2010 I tried the example from Isangree but it always repeats the first name in the list again at the end of the list. Am I doing something wrong?

stapleb
stapleb

RRB you are partially right about only one SUMIF in 2003 and earlier versions. You could get around this by doing "=SUMIF(Range,Criteria,Range)+SUMIF(Range,Criteria,Range)" and more if needed.

ragdyer
ragdyer

After you set this up once, you can just add names and amts sold to Cols A & B and *automatically* populate unique name list in Col D and totals in Col E. Assume future NAME datalist = A2:A200 Amts = B2:B200 You *don't* have to fill in those ranges now. Just populate when needed. In D2 enter: =A2 This starts the unique name list by simply copying the first name entry. In E2 enter: =SUMIF(A$2:A$200,D2,B$2:B$200) This, of course, totals that first name entry. If you wish, you can copy this down as many rows as you anticipate you might need. NOW - in D3 enter this *array* formula: =IF(ISERR(MATCH(0,COUNTIF(D$2:D2,A$2:A$200&""),0)),"",INDEX(IF(ISBLANK(A$2:A$200),"",A$2:A$200),MATCH(0,COUNTIF(D$2:D2,A$2:A$200&""),0))) Watch out for "word wrap" in this forum, which may distort this formula when you copy it. As an *array* formula, you *must* enter it using CSE (Ctrl,Shift,Enter), insted of just the regular "Enter". After you copy (or enter) the formula in D3, click back in the cell, hold down "Ctrl" & "Shift" and then hit "Enter". This will cause XL to automatically wrap the formula in curly brackets, telling you that you've done it correctly. You *cannot* manually add those brackets. Also, when you revise the formula, you must use the same CSE! NOW, *AFTER* the CSE entry in D3, copy D3 down as far as needed. This will give you a dynamic list of unique names and totals as you make future entries to Columns A & B. Good Luck Regards, RD

RRB
RRB

Lets call the C column Total, and in C2 write the function: SUMIF($A$2:$A$9,A2,$B$2:$B$9), then copy down. The total for each person would be repeated, but you could include any number of new people you wanted and have your total. Or use a Pivot Table. Note that the SUMIF function allows for only one condition in Excel 2003 and previous versions. That's why somebody suggested concatenating two or more values to get one and be able to use the function. Excel 2007 and 2010 allow for more that one condition in the function.

djones
djones

Agree it looks like 101 but maybe there are still people who need 101 so dont knock it !hehe. Thanks for the 'copy to new location, unique values' thats a new one for me and useful. Maybe that was going to be in Excel 201 :-)

sjdorst
sjdorst

This does it one time (or as many times as you want to to it manually). It will help, but I'm still looking for a way to do it FULLY dynamically - I.E. Add a new name to column A and have it magically appear in column D!

lsangree
lsangree

In Excel 2007: Data Tab >> Sort & Filter >> Advanced "Copy to another location" "Unique records only"