In this Microsoft Office Online Training article we’re going to explain how to use the SUMIF and SUMIFS Formulas, and look at a couple of different applications for them.

You can download the workbook used in this example and practice what you learn.

Learning the SUMIF formula extends the capabilities of the basic SUM formula by allowing you to tell Excel to only SUM items that meet a certain criteria.  Whilst the SUMIFS is new in Excel 2007, it allows you to stipulate multiple criteria, hence the plural.

Enough explanation, let’s dive into an example as it’s easier to visualise.

SUMIF Formula first:

The function wizard in Excel describes the SUMIF Formula as:

=SUMIF(range,criteria,sum_range)

Not very helpful is it?  Let’s translate it into English with an example.  In the table below we want to sum the total number of Units (in column D) for Dave:

Excel-SUMIF-and-SUMIFS-Formulas-explained

Translated, our formula would read like this:

=SUMIF(the name in column C, = Dave, add the figures in column D)

We could even put a summary table at the bottom of the list for each builder like this:

Our formula in cell D12 would be:

=SUMIF(C2:C7,”Dave”,D2:D7)

While the above formula is good, if we were to copy it to the rest of the summary table (cells D11 to D14 and F11 to F14) we would have to manually change the cell references and builder’s name to get the correct answers.

The solution is to use absolute references to help speed up the process of copying the formula to the remainder of column D and column F.  With absolute references our formula would look like this:

=SUMIF($C$2:$C$7,$C12,D$2:D$7)

We could then copy and paste the formula to the remaining cells in our summary table without having to modify it at all.  As we copied it down column D, Excel would dynamically update the formula to automatically pick up the next builder in the list.  Then when we copied it across to column F, Excel would dynamically alter the reference to column D to F.

The best way to fully understand this conundrum is to try it for yourself.  Download the workbook used in this example here for practice.

Note: I used a basic example to illustrate how to use the SUMIF formula, but you can also achieve this using the subtotal tool in the Data tab.  But that’s a lesson for another day!

SUMIFS Formula:

The function wizard in Excel describes the SUMIF Formula as:

=SUMIFS(sum_range,critera_range_1,criteria_1,criteria_range_2,criteria_2.....and so on if required)

Extending the SUMIF example above, say we wanted to only summarise the data by builder, for jobs in the central region.  We could use the SUMIFS formula as it allows us to set more than one condition.

Here’s how the formula would be interpreted if we wanted to add up the Units in column D, for Doug’s jobs in the Central region:

=SUMIFS(add the units in column D if, in column C, they are for Doug and, if in column B, they are also for the Central region)

Note: Excel will only include the figures in column D in the sum when both conditions (Doug & Central) are met.

In Excel our formula would read:

=SUMIFS(D$2:D$7,$C$2:$C$7,$C18,$B$2:$B$7,$B$17)
Excel-SUMIF-and-SUMIFS-Formulas-explained

Note: again I’ve used a simple example to illustrate this formula, but you could also achieve this summary table for each builder by region using Pivot Table. To watch a tutorial on how to insert a Pivot Table sign up to our Premium Microsoft Office Online Training or read our Pivot Table Tutorial.

Try other operators in your SUMIF and SUMIFS

Just like the IF formula, the SUMIF and SUMIFS are based on logic.  This means you can employ different tests other than the text matching (Doug & Central) we’ve used above.

Other operators you could use are:

  • =             Equal to
  • <             Less Than
  • <=           Less than or equal to
  • >=           Greater than or equal to
  • <>           Less than or greater than

For example, if you wanted to sum units greater than 5 the formula would be:

=SUMIF($D$2:$D$7,">5",$D$2:$D$7)

Don’t forget to download the workbook used in this example and practice what you’ve learnt.

Want More Excel Formulas

Why not visit our list of Excel formulas. You’ll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy :)

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 140 comments… read them below or add one }

PELYMINA D'SILVA July 25, 2014 at 5:41 pm

Roll No. Name of the student SCIENCE HISTORY GEOG MATHS TOTAL
1 Almeida Aloma Xena A C B E
2 Amonkar Nidhi D C A E

This is excel sheet
In the total column i want the total grade’s points
and the grade’s points are:-
for A-5, B-4, C-3, D-2, E-1

(what will be the formula)

Reply

Catalin Bombea July 25, 2014 at 10:37 pm

Hi Pelymina,
Try this formula:

=SUM(INDEX({5\4\3\2\1},MATCH(C2,{"A"\"B"\"C"\"D"\"E"},0)),INDEX({5\4\3\2\1},MATCH(D2,{"A"\"B"\"C"\"D"\"E"},0)),INDEX({5\4\3\2\1},MATCH(E2,{"A"\"B"\"C"\"D"\"E"},0)),INDEX({5\4\3\2\1},MATCH(F2,{"A"\"B"\"C"\"D"\"E"},0)))

In C2:F2 there should be the letters corresponding to grades.
It should give you the desired result.
Catalin

Reply

Donna July 8, 2014 at 5:24 am

Organization refuses to create a category and allows users to free form entry of school names. Trying to create a SUMIF to cover an array of variations of the name of the school. The SUMIF works with only one “wildcard” text name. For instance, school called “Northwest Elementary,” so I entered “*Northwest*” to include any mention of the school name. But some users abbreviated it “NWES.” How can I include a named range (my array) or multiple wildcard variations in the SUMIF formula?
SUMIFS seems to cover only AND conditions and not OR.
Would appreciate any guidance you can give me. And I VOLUNTEER for this as community service for my Master Gardener certification.

Reply

Catalin Bombea July 8, 2014 at 4:37 pm

Hi Donna,
You can try a minimum search string that covers all situations, like: “*N*Wes*”, or just “*N*W*”
If this is not working, upload a sample file at our Help Desk, for a personalized solution.
Catalin

Reply

Fakhar June 20, 2014 at 8:42 pm

Profit Sales
1000 1000
1200 2000
1300 2300
-1000 500
-2000 600
-5000 700

Above mentioned is my data. I want to sum all the sales falling next to the negative profit figure. The total/Result should be 1800/-.
Please guide.

Reply

Catalin Bombea June 20, 2014 at 9:14 pm

Hi Fakhar,
try this :

=SUMPRODUCT(($A$2:$A$7<0)*($B$2:$B$7))

Catalin

Reply

george May 17, 2014 at 10:51 pm

i have big data from 2000-10 with sales. want to count sales in 2000 >100k, 2nd field bn 101k and 500k etc. use pivot 2007

Reply

Catalin Bombea May 18, 2014 at 5:07 pm

Hi George,
Using a pivot table to manipulate large data sets is a good idea. You can use value filters on sales column to get the desired results. From your message, it’s hard to understand what you need, maybe you can give us more details and show us where you need help in this process.
Cheers,
Catalin

Reply

Guy Smith May 7, 2014 at 6:23 am

What a life-saver! Easy to understand and to apply. Thank you very much.

Reply

Mynda Treacy May 7, 2014 at 8:45 am

Thanks, Guy. Glad I could help :-)

Reply

seereen April 24, 2014 at 5:28 pm

Thank you for wondore information with proper example

Reply

Mynda Treacy April 24, 2014 at 8:15 pm

You’re welcome, Seereen :-)

Reply

Bill April 17, 2014 at 1:46 am

Mynda, I have a row of numbers, I need to sumif only the ones that are formatted in a color Like red. Thanks

Reply

Catalin Bombea April 17, 2014 at 7:04 pm

Hi Bill, you can use UDF’s, as this is not possible with regular excel functions. You can find from the web the list of excel color index, or you can use the function CellColorIndex to find the index color of your range. Then you can use that index in SumIfByColor function.
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer

Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function
Function SumIfByColor(InRange As Range, _
WhatColorIndex As Integer, SumRange As Range, _
Optional OfText As Boolean = False) As Variant

Dim OK As Boolean
Dim Ndx As Long

Application.Volatile True

If (InRange.Rows.Count <> SumRange.Rows.Count) Or _
(InRange.Columns.Count <> SumRange.Columns.Count) Then
SumIfByColor = CVErr(xlErrRef)
Exit Function
End If

For Ndx = 1 To InRange.Cells.Count
If OfText = True Then
OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex)
Else
OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then
SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value
End If
Next Ndx

End Function
Catalin

Reply

william April 16, 2014 at 6:03 am

Hi,

I need help with this one:

=SUMIFS(S3:S1000,Q3:Q1000,TODAY()-1,T3:T1000,”<=41751")

Here "<=41751" means before a specific date like 2014-04-25.
but this date is in a cell and of course can change.

Any answer ?

Thanks

Reply

Catalin Bombea April 16, 2014 at 12:54 pm

Hi,
Try this:

=SUMIFS(S3:S1000,Q3:Q1000,TODAY()-1,T3:T1000,"< ="41751)

, this way 41751 is treated as a number, not a text string…
Catalin

Reply

Lizz Higgins February 13, 2014 at 12:15 pm

How do you get a sumif H$5″,$D$2:$D$7) I can’t get a read on the date cell, but it gives me no error.

Reply

Catalin Bombea February 13, 2014 at 2:23 pm

Hi Lizz,
The formula should look like: =SUMIF($D$2:$D$7,H$5)
First argument is the criteria range, second argument is the criteria to be searched in the criteria range. The third argument is optional, and must be used only if the range to sum is different than the criteria range. As an example, if you want to sum cells from range E2:E7, but the criteria is on D2:D7, the formula will be: =SUMIF($D$2:$D$7,H$5,E2:E7)
Catalin

Reply

Mohammad Salauddin January 19, 2014 at 5:36 pm

helpful

Reply

Mynda Treacy January 20, 2014 at 8:24 am

Thanks, Mohammad :-)

Reply

Mudassir Hussain December 28, 2013 at 3:23 pm

really helpful in excel working peopele

Reply

Mynda Treacy December 30, 2013 at 8:00 pm

Thanks, Mudassir :-)

Reply

Arslan Tariq August 21, 2013 at 4:09 pm

Approved Formulas:

=SUMIFS(H2:H17,A2:A17, “>=19999″,A2:A17, “<=30000")
=SUMIFS(Sheet1!H2:Sheet1!H17,Sheet1!A2:Sheet1!A17, A2)

Optional Formulas:

=VLOOKUP(A2,Sheet1!$I:$J,2,FALSE)
=SUMIFS(H2:H17,A2:A17, "a")

Reply

Mynda Treacy August 21, 2013 at 7:51 pm

Thanks Arslan, but I’m not sure what you’re getting at. Perhaps you’d care to elaboarte?

Reply

fazilahmad August 15, 2013 at 4:26 pm

thanks from this page

Reply

Mynda Treacy August 15, 2013 at 4:34 pm

You’re welcome, Fazilahmad :)

Reply

Adam July 28, 2013 at 10:13 am

=SUMIF(C2:C7,”Dave”,D2:D7) in this formula if i want to select 2 category s names let see one is Dave and East how can i apply the formula.
Rigion & Builder

Reply

Mynda Treacy July 28, 2013 at 9:31 pm

Hi Adam,

You need the SUMIFS for that:

=SUMIFS(D2:D7,C2:C7,"Dave",B2:B7,"East")

Kind regards,

Mynda.

Reply

Vikram Chaurasiya July 2, 2013 at 8:44 pm

Respected sir/madam,

I would kindy request to you pls help me regarding this formula :- K49*10^6/$K$5/10^4 so which types of formula i can make reqarding this symbol :- ^

Thanks,
Vikram

Reply

Carlo Estopia July 3, 2013 at 10:04 am

Hi Vikram,

It would be better if you’d state what type of math
you are using here. To be honest, we are not
statisticians/engineers/mathematicians so we
will know what exact built-in function is equivalent to this
computation of yours.

If you have time, send it here: HELP DESK.

Cheers,

CarloE

Reply

Amy June 6, 2013 at 4:20 am

Hello there,

Great website! I am trying to apply your SUMIF formula to my own spreadsheet, but having trouble figuring out how to make this work because my range is a list of DATES- (formatted as 1/1/13).

Can you tell me what your formula would look like if you were sorting by date (column A) in your first example above instead of sorting by name (in your example- “Dave”). In other words, if you were looking to find out how many units were sold in a given MONTH, rather than by a given BUILDER.

Thank you much!
Amy

Reply

Mynda Treacy June 7, 2013 at 10:52 am

Hi Amy,

Here is a tutorial on SUMIF/S using dates.

Kind regards,

Mynda.

Reply

Anthony Scott May 23, 2013 at 10:30 am

Thankyou so very much!! Extremely helpful!!! Thanks again

Reply

Mynda Treacy May 23, 2013 at 2:55 pm

You’re welcome, Anthony :)

Reply

Sam May 19, 2013 at 5:32 am

Hi,

what if the “range” is dynamic, for example I don’t know in which column I need to search for the string?
in that case, can I use Match function to find the column name? how?

Can u pls help?
Thanks.

Reply

Mynda Treacy May 19, 2013 at 9:10 pm

Hi Sam,

Not really. How many columns are we talking about? Can you just do multiple SUMIFS and add them togehter?

Kind regards,

Mynda.

Reply

Ryan May 3, 2013 at 2:50 am

using the sumif formula; what if your ranges are not all next to each other. In other words, the cells that I want to use for the range and sum range are scattered throughout the worksheet.

Reply

Mynda Treacy May 3, 2013 at 1:36 pm

Hi Ryan,

No, the columns don’t all have to be adjancent to each other, but the size of the ranges should be the same e.g.:

=SUMIF(C2:C10,A1,E2:E10)

In the above formula the number of rows in the range and sum_range are 9.

Kind regards,

Mynda.

Reply

Roger May 2, 2013 at 5:57 pm

This is what I was expecting. Explanation excellent…

Reply

Mynda Treacy May 2, 2013 at 6:42 pm

Cheers, Roger :)

Reply

Manikandan.C April 26, 2013 at 10:47 pm

My Formula is =SUMIFS(Summary!M:M,Summary!F:F,”G5″,Summary!E:E,”Active”,Summary!C:C,”CHN”,Summary!N:N,”Not Bulk”,Summary!O:O,”Positive”)

Reply

Mynda Treacy April 27, 2013 at 9:03 pm

Was that a questions, Manikandan?

Reply

Manikandan.C April 26, 2013 at 10:46 pm

Thanks For the Posting

Reply

Tommy April 11, 2013 at 5:12 pm

I want to know it you can help me i battle with my sheet to date() to get some information please let me knoe if i can send my workbook for help
Thanks Tommy

Reply

Carlo Estopia April 11, 2013 at 7:23 pm

Hi Tommy,

Yes of course, please send your workbook with explanations via HELP DESK.

Cheers,

Carlo

Reply

Chris Bankes April 9, 2013 at 12:59 am

Hi,

I have a table where I want to use a sumif where it checks a PO has been raised for the month of January and give me a value. Now the month of January is in a date which has to be converted to a month. i.e. 24/01/2013 -> January. E.G.

PO Received 01/01/2013 £1,751.00
PO Received 01/01/2013 £1,035.00
PO Received 01/01/2013 £4,200.00
PO Received 07/01/2013 £146.14
PO Received 07/01/2013 £247.20
PO Received 08/01/2013 £150.50

Is there a simple way of doing this?

Reply

Carlo Estopia April 10, 2013 at 1:20 pm

Hi Chris,

Try doing this with tables and then try a formula like this:

=SUMIFS(Table1[Column2],Table1[Column1],">=1/1/2013",Table1[Column1],"< =1/31/2013")

Assuming the table below.

Column1	    Column2
1/1/2013	1
1/2/2013	1
1/31/2013	1

In a nutshell, to create a table, simply highlight the range (including headers) of your data.
Then, go to insert ribbon and click 'Table'. Once you have the table you can now reference them
like I did with the formula above.

Read more: Tables, SUMIFS

Note: You must make sure your criteria date and your dates must be consistent as to format.

Cheers,

CarloE

Reply

dennis March 12, 2013 at 7:12 am

Hello,

I am having a problem using Not Equal to in SUMIFS formula where the not equal to value is referenced. These don’t work as my range name and cell reference are essentially text.

sumifs(a:a,b:b,”rangename”) or
sumifs(a:a,b:b,”c1″)

I have tried a bunch of syntax options, but cannot accomplish the task.

I need it to refer to another cell or range name that is derived from user input and not have it hard coated into the formula.

Any suggestions would be appreciated.

Thanks

Reply

Carlo Estopia March 12, 2013 at 1:18 pm

Hi Dennis,

Assume this data at A1:B2 :

Range	12
Go	12

Now use formula in this one:

=SUMIFS(B1:B2,A1:A2,"<>Range")

or you may add a named range for Range A1:A2 and name it YourRange or whatever your range name is.

=SUMIFS(B1:B2,YourRange,"<>Range")

Read More: Named Range

Cheers.

CarloE

Reply

dennis March 14, 2013 at 11:30 pm

Apologies, the example translated poorly in this text box.
it was a list of values for each fruit, sum for each and a sample of each formula type trying to exclude one of the reference columns.
D / E
Apple /3
Orange/8
Pear/1
Apple /5
Orange/2
Pear/6
Apple/9
Orange/7
Pear/4

I was trying to exclude Apple in the formula as either a referenced cell or range name. The only correct answer (28) was when the text value was included in the formula.

=SUMIFS(E:E,D:D,”$A$1″) = 45 incorrect
=SUMIFS(E:E,D:D,”Apple”) = 28 correct
=SUMIFS(E:E,D:D,”AP”) = 45 incorrect with AP being a range name

i tried a bunch of alternate format options and they all resulted in errors. (original example had a column total that doubled the counts)

Thanks

Reply

Carlo Estopia March 15, 2013 at 9:34 am

Hi Dennis,

Don’t put double quotations around your criteria when you want it to be interpreted as other than text/string.

Example:
referencing a cell

=SUMIFS(E1:E10,D1:D10,"<>" & D3)

where D3 contains Apple

referencing a named range

=SUMIFS(E1:E10,D1:D10,"<>" & YourNamedRange)

where YourNamedRange contains Apple

Cheers.

CarloE

Reply

R Smith March 8, 2013 at 7:06 am

Thank you!

Reply

Jason Forrest February 28, 2013 at 9:44 am

Hi,

I can’t seem to get any SUMIF functions to work. I have excel 2007 and I downloaded your Excel_Blog_Workbooks file, however When I click on a SUMIF cell and then either click off it or try and press enter, I get a “The Formula you typed contains an error” even though I never changed anything and the formula in theory should work.

Any suggestions?

Reply

Carlo Estopia February 28, 2013 at 12:21 pm

Hi Jason,

I tried to download the file and nothing is wrong even as I simulated the actions
you did that created or triggered the error.

I suggest it is about your Excel’s regional settings or something.
I had this German project before and there are some differences like
We used dots/periods for decimals while they use comma’s for it.
In formulas, we use comma’s to separate arguments while they use
semi-colons. So I’m just saying maybe it has some relevance to your
problem.

So I suggest this:

Change the country or region setting

The country or region setting in Windows, referred to as location, indicates the country or region that you are in. Some software programs and services provide you with local information, such as news and weather, based on this setting.

    Open Regional and Language Options by clicking the Start button Picture of the Start button, clicking Control Panel, clicking Clock, Language, and Region, and then clicking Regional and Language Options.

    Click the Location tab, select your location from the list, and then click OK.

source: Microsoft Support

Cheers.

CarloE

Reply

Jory February 28, 2013 at 2:42 am

Using the SUMIF formula I am summing amounts on a different tab in the same workbook as my unique identifier (in this case, employee ID). If I were to sort the rows on the tab where the formula is entered, using the filter button, my SUMIF formula doesn’t completely update. It is almost as if the formula contained an absolute reference ($’s).

=SUMIF(‘Payroll Detail’!A:A,’Expected Payouts’!F85,’Payroll Detail’!G:G)

The “‘Expected Payouts’!F85″ portion of the formula is the portion that doesn’t get updated. This formula is on the “‘Expected Payouts’” tab and the information I am trying to sum is on the “Payroll Detail” tab. So what happens is when I sort my information on the Expected Payouts tab (which does include the column where the formula is entered), this particular line was on F85 but now is located on F10 however the formula does not update to F10 like a relative reference formula would. Is this just a constraint of this formula or is there something I am doing wrong?

I appreciate any assistance you can provide. This website has been most helpful so far.

Reply

Carlo Estopia February 28, 2013 at 12:11 pm

Hi Jory,

I suspect you’re putting an absolute reference to your criteria. (see italics)

=SUMIF('Payroll Detail'!$B$2:$B$10,$A$2,'Payroll Detail'!$C$2:$C$10)

What you should do is simply lose the dollar($) signs. So what you should do is maintain
the absolute references in your SUM range and Range and change your Criteria range from $A$2 to
simply A2 –just an illustration. Do this to your first formula and drag it down as applicable.

=SUMIF('Payroll Detail'!$B$2:$B$10,A2,'Payroll Detail'!$C$2:$C$10)

I hope it helps.

Cheers.

CarloE

Reply

IS January 22, 2013 at 4:06 am

Mynda,
You can skip my question, I checkled your “named ranges” tutorial. Great help. Many thanks

Reply

Mynda Treacy January 22, 2013 at 3:04 pm

Great, glad you figured it out, Igor :)

Reply

Igor Salazar January 22, 2013 at 3:47 am

Hi – great site… I’m trying to use the SUMIF function using a formula as condition, but doesnt work…
SUMIF(A1:A5;”<$B$7;C1:C5)… The problem is that exceld oesnt recognize B7 as an active cell but as text.

Any idea on how to solve?
Thanks

Reply

tommy January 8, 2013 at 6:07 pm

Please HELP

I do have a sheet like…..

A1 = 30
A2 = 40
A29 = 20
A30 = 0
A31 = 0
Sum(a31)
And i dont want the reading in A31 i want the last active reading in this cell

Thanks Tommy

Reply

Mynda Treacy January 8, 2013 at 8:43 pm

Hi Tommy,

I’m sorry, I don’t understand what you mean by ‘reading’. Can you please explain a different way?

Kind regards,

Mynda.

Reply

Bob Cronin January 8, 2013 at 5:20 pm

Hi Mynda,
Have to admit you have a way of explaining things that really works :)

I have got myself in really deep and need some help if it’s possible.

I have a worksheet some 5000 lines long
B1 is selected; I need to add the rows from the cell one below selected to the cell one above the row that holds both an S in column A and a 0 in column C.
The number of rows varies greatly between occurrences. =SUMIF appears to be the way to go, but how do I set the bottom limit.
=SUMIF(B2:B?,A2:A?,”P”,C2:C?,>0)
I realise once the formula is there I can filter the page using the “0” to get the lines I need and paste it in to the cells I want, but I just cannot get my head around this. Dumb plumber here :)
A B C
1 S 0
2 P 5.1876 1
3 P 0.25608 1
4 P 0.14784 1
5 P 20.79 1
6 P 12.9532 1
7 P 8.976 1
8 P 5.94 1
9 S 0
10 P 5.1876 1
11 P 0.25608 1
12 P 0.14784 1
13 P 16.34579 1
14 P 8.976 1
15 P 7.49571 1
16 P 40.81 1
17 S 23.3904 1
18 P 2.794 2
19 P 0.8536 2
20 P 0.4928 2
21 P 19.25 2
22 S 0
23 P 1.727 1
24 P 0.8536 1
25 P 0.4928 1
26 P 19.25 1
27 S 0
28 P 3.905 1
29 P 0.8536 1
30 P 0.4928 1
31 P 19.25 1
32 S 0

Reply

Bob Cronin January 8, 2013 at 5:22 pm

Sorry, cannot edit out what looked like nice columns before i hit send :)

Reply

Mynda Treacy January 8, 2013 at 8:42 pm

Hi Bob,

SUMIF/S won’t work in this case because you need the criteria on the row you want to sum, not before/after. For example; row 17 has an S in column A but 1 in column C, so if I understand you correctly you wouldn’t want to sum rows 18-21.

I’m not sure how you can go about this. You ideally need an identifier on each row that classifies the row as something you want to add or want to exclude. Is there any other data you could use to identify the rows you want to include?

Mynda.

Reply

Bob Cronin January 9, 2013 at 9:00 am

Morning Mynda.
Thanks for you reply.
I have uploaded an image file to http://dl.dropbox.com/u/4688931/excel%20grab.gif
The yellow lines are the ones I need to populate with results. As you can see the number of rows between each yellow line varies. E3:E9 needs to be in E2, I3:I9 needs to be in I2, then down a yellow line and I need E11:E17 needs to be in E10, I11:I17 needs to be in I10 ……..
“Cost”, “TotalMaterialCost”, “LabourTotalCost”, “LabourTotalTime” columns needs to sum the values between the yellow lines. E2, I2, K2, M2. Then E10, I10, K10, M10 …….. on and on.
I thought =SUMIFS using the “S” in column A and the “0” in column N would work, but each way I try I’m wrong

Reply

Mynda Treacy January 9, 2013 at 3:16 pm

Hi Bob,

SUMIFS definitely won’t work, for the reasons I cited earlier.

I could probably come up with a complicated formula using OFFSET to do this for you but fundamentally the way you have the data laid out isn’t ideal.

What you have here is a combination of a report and a database which makes your data incredibly inflexible for analysis purposes, and application of formulas like SUMIFS.

Ideally you would have 1 more column to house the Dxxxx which would mean you could then use some of the built in tools like Subtotals or PivotTables to automatically summarize the data for you.

e.g. Rows 3:9 would have a D10102 in column A, rows 11:17 would have a D10103 in column A and so on. You would get rid of rows containing S as Subtotals or PivotTables will automatically put these in for you.

How much work is it for you do change the worksheet to this layout?

Mynda.

Reply

Bob January 10, 2013 at 8:25 am

Mynda,
The file is changable just add a new column not a problem, but am I expecting to much from you?

SHAUKAT RAZAQ December 22, 2012 at 8:13 pm

Hi,
I need a best formula for MS Plates Inventory in MS Excel,
Please help me.

Best Regards

Reply

Mynda Treacy December 23, 2012 at 8:32 pm

Hi Shaukat,

I’m sorry, I don’t know what MS Plates is.

Kind regards,

Mynda.

Reply

Dee November 11, 2012 at 5:10 pm

Hi,
Can you please help me with the SUMIFS example you explain (shown below) HOw would I add a second criteria to my total eg. I want a total units of both Doug and Brian under Central as one total. Not just Doug. I have tried everything but am now lost.
Your formula to get Doug’s total below reads:
=SUMIFS(D$2:D$7,$B$2:$B$7,$B$17,$C$2:$C$7,$C18)

SUMIFS Central Units Average $k Total $k
Total Doug 8 389 3,112
Total Dave – – –
Total Brian 8 730 5,840
Total Larry – – –
16 560 8,952

I want to be able to have “Doug & Brian” if Central as one sum called say for example… D&B?
Hope you can help me.
Many thanks
Dee

Reply

Mynda Treacy November 12, 2012 at 7:05 am

Hi Dee,

To sum Doug and Brian requires an OR argument, however SUMIFS can only handle AND arguments.

For example SUMIFS formulas read with AND between each criteria. SUM Units where the region = central and the builder = Doug OR Brian. It can’t handle the OR which means you can only choose one criteria from each column.

What you need is the SUMPRODUCT function like this:

=SUMPRODUCT((D2:D7)*(B2:B7=”Central”)*((C2:C7=”Doug”)+(C2:C7=”Brian”)))

The + operator between the Doug and Brian criteria in the above formula tells Excel to SUM Doug OR Brian.

You can read about how SUMPRODUCT works here.

Kind regards,

Mynda.

Reply

Tyson October 30, 2012 at 6:26 am

Having trouble with this: I have a list of dates in one column, profit and loss in another column. I want to be able to keep running totals for each month on another column. do I set the dates and P/L columns in an array and extract the dates or I was using sumif but I couldn’t figure out how the keep a total in the month column being some months will have different numbers of dates.(I.E. Jan might have one transaction, Feb might have 10, March might have 5). I am having trouble with the date ranges in the dates columns \.

Reply

Mynda Treacy October 30, 2012 at 8:56 am

Hi Tyson,

Let’s say your dates are in column A and your P&L figures are in column B, then you can use this formula:

=IF((MONTH(A2)=MONTH(A1)),C1+B2,B2)

Note: row 1 needs to be blank…or the row above your first formula needs to be blank.

Kind regards,

Mynda.

Reply

Tessie October 18, 2012 at 9:23 pm

Thanks for the free download

Reply

Mynda Treacy October 19, 2012 at 7:27 am

You’re welcome, Tessie :)

Reply

AEH September 18, 2012 at 11:06 am

Hey Guys,

Can any help me with a formula? Basically what i’m trying to achieve is:

If the total of a cell is greater then x minus a per centage from the amount it is over by.

So for example if x is 7500, cell total is 7600, minus 50%, total should be 50.

Can anyone help?

Reply

Philip Treacy September 18, 2012 at 11:32 am

Hi AEH,

So if I understand this correctly you have something like this in your sheet :

A1 = 7500
B1 = 7600
C1 = Your result

So in this case the formula you want in C1 is :

=IF((B1-A1)>0,(B1-A1)*0.5,0)

which translates as :

IF B1-A1 > 0, then the result is (B1-A1)*0.5, otherwise the result is 0

Cheers

Phil

Reply

Tim September 17, 2012 at 1:49 am

Your comment, “Not very helpful is it?” is typical of my opinion of Microsoft help. However, your tutorial did help me understand SUMIF function. Thanks.

Reply

Mynda Treacy September 17, 2012 at 7:27 am

Cheers, Tim. I’m glad I could help you :)

Reply

Shyam September 4, 2012 at 3:47 pm

Hi Mynda,

Well, I am running out of words to admire the way you explain everything about excel….it is right to say that I am excelling in excel :-)
But, I am at a point that I need extra help from you. I need more questions to practice on every condition, formula, etc., in excel. I googled for it, but no luck. I hope my search ends here! I appreciate your response.

Reply

Mynda Treacy September 4, 2012 at 5:06 pm

Hi Shyam,

Thanks for your kind words. I find a great place to get practice is by helping answer questions in forums. You often find challenges you wouldn’t have thought of, and as other members of the forum answer the question you are likely to learn a few different ways to solve the problem.

Just type “Excel Forum” into Google and you’re sure to find some.

I hope that helps.

Kind regards,

Mynda.

Reply

Tommy September 3, 2012 at 4:52 pm

Thanks for your simple but clear demonstration of Excel formula.

Reply

Mynda Treacy September 3, 2012 at 6:35 pm

Cheers, Tommy :)

Reply

Ian August 17, 2012 at 9:13 pm

SUMIF examples using dates would be nice….and SUMIF accross multiple spreadsheets.

Reply

Mynda Treacy August 17, 2012 at 9:40 pm

Hi Ian,

Here is SUMIFS using dates.

Here is SUMIF across multiple worksheets.

I hope that helps.

Kind regards,

Mynda.

Reply

Pierre Roody August 17, 2012 at 6:01 am

Good evening, this is the work you do, you have given me much help. I thank you already.
My problem with the function sumifs is that I would add the cells found in SEVERAL worksheets with four conditions.
help me stp. thank you in advance

Reply

Mynda Treacy August 17, 2012 at 3:08 pm

Hi Pierre,

You could try the 3D SUMIFS. Actually this tutorial is for SUMIF but you could use SUMIFS instead.

Kind regards,

Mynda.

Reply

Dean August 14, 2012 at 12:36 pm

This is a great help.
How can i bring this further by summing if using a word that is only part of a longer word.
For example I want to sumif the word “Apple” is in the cell.
Possible entries are: Green Apple, Red Apple, Granny Smith Apple.
Thanks.

Reply

Mynda Treacy August 14, 2012 at 1:09 pm

Hi Dean,

You can use wildcards to search for parts of words:

=SUMIF(range,”*apple*”,sum_range)

Kind regards,

Mynda.

Reply

Jay August 2, 2012 at 4:15 am

Thanks for the sharing your knowlege

Reply

Mynda Treacy August 2, 2012 at 7:40 am

:) You’re welcome, Jay.

Reply

Brian July 28, 2012 at 4:01 am

You have a very nice website. Thank you for the helpful formulas and especially the easy to understand explanations.

Here is my problem and thanks in advance…I am guessing it is a multiple sum if but am having a hard time finding an example that is close.

For a group project award payout:
1 to 3 team members get payed 2000 each.
Maximum payout is 2000 up to 3…1 person does NOT get 6000

If total teambers is greater than 3 the payout is 6,000 divided by total number of team members.

I am listing each participant on on my excel spreadsheet and want to display the amount of pay each person wil receive

Scenario 1: Total of 1 Player
Team Member 1: $2000

Scenario 2: Total of 3 Team Players
Team Member 1: $2000
Team Member 2: $2000
Team Member 3: $2000

Scenario 3: Total exceeds 3 team members so maximum payout of 6,000 is divided by total of team players
Team Member 1: $1500
Team Member 2: $1500
Team Member 3: $1500
Team Member 4: $1500

Reply

Mynda Treacy July 29, 2012 at 11:50 am

Hi Brian,

You could use this formula:

=IF(COUNTA(team_members)>3,6000/COUNTA(team_members),2000)

Where the team_members is the named range containing the list of your team members.

Kind regards,

Mynda.

Reply

John July 27, 2012 at 12:35 pm

Thanks for your site. I am trying to figure out how to use SUMIF, and I thought that you might have solved the problem, but then I ran into a dilemma. You state that you can use the formula:
=SUMIF(A1:A7,$H$7,B1:B7)
AND
You can use the formula
=SUMIF(A1:A7,”>5″,B1:B7)
So, why can’t I use something of the form:
=SUMIF(A1:A7,>=$H$7,B1:B7)
OR
=SUMIF(A1:A7,”>$H$7″,B1:B7)

The first two give me an answer, but the last one bombs out.

I am trying to write a formula that would scan a column for a date that is less than a particular date. And then I want to sum all of the dollar amounts associated with the dates that are less than or equal to that date.

Got any ideas?

Thanks for your help.

John

Reply

Mynda Treacy July 27, 2012 at 8:01 pm

Hi John,

I wrote a tutorial on SUMIFS referencing dates here because it can be a bit tricky.

Hopefully you will find your solution on that post.

Kind regards,

Mynda.

Reply

bob April 30, 2013 at 5:56 am

John,

I think you’re looking for the following syntax for =SUMIF(A1:A7,”>$H$7″,B1:B7)

=SUMIF(A1:A7,”>”&$H$7,B1:B7)

Reply

aliciajoohee June 23, 2012 at 1:59 pm

wow i’ve learnt a lot so far through this website, it is the best site about excel i’ve found. wowww im so grateful. you’re really awesome, thanks for teaching, sharing those tutorial.

Reply

Mynda Treacy June 23, 2012 at 2:19 pm

Thanks, Alicia :)

Mynda.

Reply

SHWETHA January 3, 2013 at 6:32 pm

i want to know whether we can count 1.5 in the cell, for example =SUM(COUNTIF(U5:AY5,”P/P”),COUNTIF(U5:AY5,”P/P”)+SUM(COUNTIF(U5:AY5,”P”)+SUM(COUNTIF(U5:AY5,”P4″,1.5)))
just help me out with this

Here P should be 1
p/p should be 2
and p4 should be 1.5

Reply

Mynda Treacy January 3, 2013 at 9:12 pm

Hi Shwetha,

You can add COUNTIF formulas but you don’t need the SUM function as well. e.g.

=(COUNTIF(U5:AY5,"P/P")*2)+COUNTIF(U5:AY5,"P")+(COUNTIF(U5:AY5,"P4")*1.5)

Kind regards,

Mynda.

Reply

Greg McIroy June 16, 2012 at 4:35 am

Hi … wonderful site you have here. You communicate the training quite well. Unfortunately, I am still left wondering about the SUMIF.

I want to sum the values in column A (A1:A10) when the first character in text column B (B1:B10) is a 2.

I know I can create a new column C using LEFT(B1,1) and use SUMIF(C1:C10,2,A1:A10), but I’d like not to create a new column.

Any ideas? Thanks!

Reply

Mynda Treacy June 16, 2012 at 8:27 pm

Hi Greg,

You can use wildcards in your SUMIF to find text in column B that begins with a 2. Like this:

=SUMIF(B1:B10,”2*”,A1:A10)

The asterisk is your wildcard.

I hope that helps.

Kind regards,

Mynda.

Reply

Greg McIroy June 23, 2012 at 12:09 am

Thanks so much Mynda. It’s nice that you can come up with the simplest solution for something that I over-complicated.

Have a great day!

~GregM

Reply

Mynda Treacy June 23, 2012 at 2:20 pm

:) You’re welcome.

Reply

Lewis April 20, 2012 at 9:40 pm

Hi there. I’m trying to create a recruitment report regarding successful/unsuccessful applicants and unsure how to create a formula where if they are successful, the total is counted.

Just now, I am trying this: SUMIF(K2:K20, \=Yes\, K2:K20) but it comes up as zero.

Your help would be appreciated!

Reply

Mynda Treacy April 22, 2012 at 8:45 pm

Hi Lewis,

Let’s say in column B you have the word “successful” or “unsuccessful” you could use the COUNTIF function like this to count the successful applicants:

=COUNTIF(B2:B20,”successful”)

or like this to count the unsuccessful applicants:

=COUNTIF(B2:B20,”unsuccessful”)

More on the COUNTIF Function.

Kind regards,

Mynda.

Reply

Jim Baran February 22, 2012 at 5:55 am

Hope this helps me.

Reply

Gary Petersen February 7, 2012 at 1:38 am

Odd, it looks like my comment got garbled. I had written more than that. What I was trying to convey is this.

The SUMIFS formula works quite well when you define the criteria specifically in the formula, but not so well when you tie the criteria to a cell reference. For example, if were to add a date of 2/1/2008 in cell G1 and desire to sum all of the units sold after that date, I think the formula would look like this:

=SUMIFS(D2:D7,A2:A7,>=G1)

I get a result of 0 when I input that formula, however. If I put the actual number representing 2/1/2008 in the formula, it works. The Excel number for that date is 39479. That forumula would look like this:

=SUMIFS(D2:D7,A2:A7,>=39479)

It returns the correct value of 36. I didn’t follow your thought about using the & reference. Can you explain further?

I understand that the SUMIF formula would work for a single criteria, such as my example. What I’d like to do, however, is define two dates and set up the SUMIFS formula to total everthing between them. The problem exists whether one or two criteria are defined.

I’ll check back to see if this comment goes through okay. If it doesn’t, I’ll put a file on my web page with the information. Thanks, Gary

Reply

Gary Petersen February 4, 2012 at 6:29 am

I’m having trouble with this function when I try to use a reference to a cell with a >= or =G1″,$A$2:$A$7,”=G1″ with the number for that date and “<G2" with the number for that date, it works. I'll download your spreadsheet later and will try it, but it isn't working withi mine. Any ideas?

Reply

Mynda Treacy February 4, 2012 at 10:20 pm

Hi Gary,

I’m not 100% clear on your question but I have a feeling that if you try to format your references using >= or =”&A7,criteria2_range,”= are in double quotes and they’re joined to the cell containing the date with the ampersand.

I hope that helped you out, but if I was off the mark please let me know with the exact sample of your formula that isn’t working.

Kind regards,

Mynda.

Reply

Zbig June 14, 2012 at 1:53 am

Mynda,

Thanks for the explanation on how to use a cell reference in the expression for the test criteria in the SUMIFS formula. I have been trying for quite some time to find a good explanation on how to use this method of using a reference to a cell as opposed to a constant and I finally managed to make it work.
Thanks.

Reply

Mynda Treacy June 14, 2012 at 7:55 pm

Thanks Zbig,

Glad you liked it.

Kind regards,

Mynda.

Reply

Pauline February 2, 2012 at 9:33 am

Mynda..Wow..Great explanation! I finally understand the spreadsheets that were passed down to me at work! Thank you so much.

Reply

Mynda Treacy February 2, 2012 at 8:20 pm

Thanks Pauline :)

Glad I could help.

Kind regards,

Mynda.

Reply

Leo January 24, 2012 at 4:44 am

Hoping for sucess

Reply

Philip Treacy January 24, 2012 at 10:24 pm

hope we can help you achieve it Leo

Reply

Leo January 24, 2012 at 4:37 am

HI

Reply

teay January 10, 2012 at 4:04 pm

good explanation

Reply

Mynda Treacy January 10, 2012 at 9:06 pm

Thanks Teay!

Reply

Glenn November 3, 2011 at 1:37 pm

Hi having a problem understanding the meaning and function of the “” and “=E” in the following formula.

IF(SUM(L10:P10)=0,”",SUMIF(L10:P10,”")-SUMIF(L10:P10,”=E”))

can anyone help me out here. to give context, the formula sums student marks and the values are numeric
thanks

Reply

Glenn November 3, 2011 at 1:42 pm

the formula as shown above is not pasted correctly. the first sumif should have a “not equal” between the inverted commas. I am not sure how this “not equal” is working (ie its intended function)

Reply

Mynda Treacy November 3, 2011 at 9:08 pm

Hi Glenn,

When double quotes are used in a formula the are referring to a blank cell. Anything within double quotes, like your “=E” example stipulates that the contents is text.

Your formula will not work since it’s telling Excel to:

IF the SUM of L10:P10 = 0, then put nothing in the cell, otherwise SUMIF L10:P10, where the contents of the cell is empty, minus SUMIF L10:P10, where the contents of the cell is =E.

This formula is incomplete. A SUMIF needs two ranges, the first range is the range with your criteria, the second range contains the values you want to sum.

So for example your formula might read: SUMIF(L10:P10,”",L11:P11) which says sum the range L11:P11 where the corresponding cells in the range L10:P10 are empty.

If you want to send me your workbook I’ll take a look at the formula for you. Just complete a ticket on the help desk.

Kind regards,

Mynda.

Reply

glenn November 3, 2011 at 11:09 am

I am trying to figure out the function of “” and “=E” in this Sumif formula

IF(SUM(L10:P10)=0,”",SUMIF(L10:P10,”")-SUMIF(L10:P10,”=E”))

the cells L:P are numerical and represent student marks.
thanks

Reply

Scott September 25, 2011 at 6:28 am

in earlier versions of Excel, before SUMIFS, isn’t there a method of multiple criteria summation using SUMIF(AND… or IF(AND…Sum(…?
I have tried several attempts but so far unsuccessful.

Reply

Mynda Treacy September 26, 2011 at 7:13 pm

Hi Scott,

Yes, there is an alternative to the SUMIFS function if you don’t have Excel 2007 or 2010. It’s called the SUMPRODUCT function and you can see examples of how you can use it to achieve the same results as SUMIFS here:

SUMPRODUCT an alternative to SUMIFS

Kind regards,

Mynda.

Reply

Sunil August 12, 2011 at 10:46 am

=SUMIFS(D$2:D$7,$C$2:$C$7,”Doug”,$B$2:$B$7,$B$17)

Reply

Mynda Treacy August 12, 2011 at 11:03 am

Hi Sunil,

Have you downloaded the workbook I used in the example above and reverse engineered how mine is working? This might help you figure out what’s going wrong with yours.

Otherwise, drop me an email with your example and I’ll take a look. Just log a ticket on the help desk.

Kind regards,

Mynda.

Reply

Sunil August 12, 2011 at 10:44 am

Thanks for the post. I’ve been using SUMIFS but lately I have been having challenges making the “” operator work for non-numeric ranges.

Like in your example, if I want to add all the units for Builder other than Doug, the formula
=SUMIFS(D$2:D$7,$C$2:$C$7,”Doug”,$B$2:$B$7,$B$17)
doesn’t seem to work.

Reply

Sunil August 12, 2011 at 10:45 am

Sorry, I meant to put a “” before the word “Doug” in the formula..
=SUMIFS(D$2:D$7,$C$2:$C$7,”Doug”,$B$2:$B$7,$B$17)

Reply

andrew christian August 12, 2011 at 3:53 am

thank you for this blog

Reply

Carmine Milbourne January 27, 2011 at 6:15 pm

thanks to the author for taking his time on this one.

Reply

Adam January 21, 2011 at 8:03 am

I want to say your blog is kinda awesome. I always like to read something new about this because I have the similar blog in my Country on this subject so this help´s me a lot. I did a search on the issue and found a good number of blogs but nothing like this.Thanks for writing so much in your blog.. Greets, Adam

Reply

Graig Liedtke January 1, 2011 at 12:42 pm

Amazing website. I am going to want some time to think about the website=)

Reply

Stiller January 1, 2011 at 6:43 am

This is really a great article! I have only one question. How do you do it? Everything on this site is so good.

Carry on the nice work!

Reply

dart December 31, 2010 at 12:07 am

Great blog , thanks for the post!

Reply

JumgrerbJek November 26, 2010 at 9:34 am

thanks

Reply

Mynda September 29, 2010 at 8:33 pm

@Kim, glad I could help.

Reply

Kim September 22, 2010 at 10:37 am

Thanks for this. I’ve always found these difficult but this helped lots :)

Reply

Mynda Treacy January 10, 2013 at 7:16 pm

Hi Bob,

I can’t change the file for you, you need to do that…especially since you only sent me an image. Once that is done you can read through the tutorial for Subtotals or PivotTables and generate the report your need using either of those tools.

If you get stuck I’m happy to help.

Kind regards,

Mynda.

Reply

bob January 11, 2013 at 2:53 pm

Thank you very much for the information, im away from my PC just have ipad for net few days but will do as you suggested. And i will let you kno0w how it goes

Thanks for the help :)

Reply

Previous post:

Next post: