This tutorial explains the difference between the SUMIF and SUMIFS functions in terms of their syntax and usage, and provides a number of formula examples to sum values with multiple AND / OR criteria in Excel. Continue reading
by Svetlana Cheusheva, updated on
This tutorial explains the difference between the SUMIF and SUMIFS functions in terms of their syntax and usage, and provides a number of formula examples to sum values with multiple AND / OR criteria in Excel. Continue reading
Comments page 3. Total comments: 234
Svetlana,
First, thanks for the very long post, and for the many responses to comments. I am a little stuck on one of the formulas you provided, in "How to use SUMIFS in Excel - formula examples," specifically, in example two, where you use the TODAY formula. I tried to tweak it like this:
=SUMIFS(E3:E1000,D3:D1000,"="&MONTH(A2),D3:D1000,"="&YEAR(A2))
This is a sample of what my data looks like (column C is empty, and column B is where I'm pasting my formula):
A, B, D, E
Month-End Date, Month-End Total Fees, Date Fee Assessed, Amount of Fee
11/30/2014, [formula - should equal $9.00], 11/15/2014, $5.00
12/31/2014, [formula - should equal $1.00], 11/21/2014, $4.00
01/31/2015, [formula - should equal $0.00], 12/03/2014, $1.00
02/28/2015, [formula - should equal $2.00], 02/05/2015, $2.00
In column A, each month will only show up once, but in column D, it might show up many times, or never.
I am simply getting zero in all cells in column B.
Thank you for any help you can give.
Robert
I figured something out. Thought I would share it here:
=SUMPRODUCT(--(YEAR(D$3:D$10000)=YEAR(A3)),--(MONTH(D$3:D$10000)=MONTH(A3)),(G$3:G$10000))
I know it's not a SUMIF function, but in case anyone was heading down the wrong path, like I was, it might help. And no, I don't know how it works. I adapted it from #6 in this thread: https://www.mrexcel.com/forum/excel-questions/613222-use-formula-sumifs-criteria-range.html, using trial and error. Sorry if this is not kosher, to post a link to another site.
Robert
What will be the formula in above example,
If I want a sum of Qty for criteria ;
Product = Apple and/or Product = Banana
Supplier = John
Hi Pradeep,
In this case, you can add up 2 SUMIFS functions:
=SUMIFS(C2:C9, A2:A9, "apple", B2:B9, "John") + SUMIFS(C2:C9, A2:A9, "banana", B2:B9, "John")
Thanks Svetlana for quick reply,
So does that mean we can give 2 criteria for single column in a single function ?
Pradeep,
In this particular example, we add up 2 different SUMIFS functions because you want a formula to work with the OR logic.
Yes, you can supply 2 criteria for a single column in a single function, but they will work with the AND logic. For example, the following formula sums values in A2:A11 that are greater than 5 AND less than 10:
=SUMIFS(A2:A11, A2:A11, ">5", A2:A11, "<10")
just to add in above comment that the sum needs to be done only for rows where the value of column A is A/C.
Hi Sufi,
Here you go:
=IF(A1="A/C", C1-B1, "")
hi, i want to count the planned qty against till date. i have used formula sumif(date column, date cell ref, qty column). unable to get the results. where as if i try a numerical no in place of cell ref, this works. can anyone helps.
B C
Jul-15 Plan
1 0
2 0
3 0
4 180
5
6 0
7 180 8 date
8 180 540
9 0 0
SUMIF(B4:B12,"<=8",C4:C12) - this works
SUMIF(B4:B11,"<=M10",C4:C11) - this does not work
Hi Reddy,
In SUMIF arguments, the correct syntax for cell references is as follows:
SUMIF(B4:B11,"<="&M10,C4:C11)
yes I also have the same problem If I give cell reference it does NOT work instead if I give the cell content it works! Any solution please?
example if A1 has a numeral 123 and if i put criteria as >A1 it does NOT work but if I redo as >123 it works!
Ganesh,
Make sure you enter the cell reference like ">"&A1
Your Sumifs explanation is great! I have a question you may have covered but I didn't find it:
As in your example of fruit, I need a formula that determines the quantity(C) based on the name(B)and product(A). In other words a multi array sumifs. I've tried just separating the sumifs sections with commas(,)but that doesn't work.
Thanks for any help you can give me.
Ann
Hi Ann,
The formula from the "fruit" example should work for your task as well, e.g:
If your Excel shows a message like "We found a problem with this formula...", most likely the List Separator is set to semicolon rather than a comma in your Regional settings. In this case, separate the SUMIFS' arguments with semicolons, for example:
=SUMIFS(C2:C9; A2:A9; "product"; B2:B9, "name")
To check this, you can star typing SUMIFS in the formula bar and Excel will display the correct syntax for you. You can also check out the List separator in the Control Panel > Region and Language > Additional Settings.
=SUMIF(A1:A6,{"abc","efg"},C1:C6)<10
this formula not working
i want to change background color if sum of cells following the criteria =SUMIF(A1:A6,{"abc","efg"},C1:C6) is less than 10
Hello Burhan,
You cannot use arrays in SUMIF's arguments. Instead you can add up the results returned by 2 SUMIF functions:
=SUMIF(A1:A6,"abc",C1:C6) + SUMIF(A1:A6, "efg",C1:C6)
And then create a conditional formatting rule with the following formula:
=$D$1<10
Where D1 is the cell containing the SUMIF()+SUMIF() formula.
I'm trying to use the "sumifs" formula with multiple criteria and I just can't seen to get it to work correctly.
I have a summary tab in which I want to pull certain data by month, and I also have a tab called "Payments" where I want to just load payments for the whole year rather than having a separate tab for each month.
I want to grab the total of payments which are in column F
Column G shows the payment method (Check and ACH) and I only want ACH payments
Column D shows the payment date and in the formula below I am looking for only February payments
So, my criteria is: Total of ACH payments in February.
Here is my formula:
=SUMIFS(Payments!$F:$F,Payments!$G:$G,"ACH",Payments!$D:$D,">=2/1/2015",Payments!$D:$D,"<=2/28/2015")
Can anyone help me figure out why it isn't working? I have tried to add the apostrophe's around payments to indicate the tab but they keep disappearing...ie: 'Payments!'
Hi Erika,
Your formula works just fine on my test sheet. How exactly isn't it working on your side?
=SUMIFS($C$2:$C$3168,$A$2:$A$3168,">=90000", $A$2:$A$3168, ">=93599", $B$2:$B$3168,J2)
Any ideas as to why this formula is not working?
C = my totals
A = Zip Code Range
">=90000" - a zip code
B = a state code
Any ideas would be greatly appreciated!
Hi Suzanne,
In your formula, you specify 2 conditions for the same range in column A (">=90000" and ">=93599"). The first one is redundant or probably you meant something different?
Hi there
I would like to sum column D, providing that certain criteria are met. One of my criteria reference values in a range of cells.
Here is my formula, the answer need to be 40
Sales in the west =SUM(SUMIFS(D1:D5,A1:A5,"sales",B1:B5,"west",C1:C5,A13:A20))
A B C D
1 sales west mark 20
2 purchace west albert 10
3 sales north john 50
4 sales south shane 30
5 sales west Dina 20
13 albert
14 charles
15 catherine
16 Dina
17 henry
18 jason
19 john
20 mark
Hi Alfred,
Try this one:
=SUMIFS(D1:D5, A1:A5, "sales", B1:B5, "west")
I am trying to take overtime and regular hours out of one column and put into two columns.
i have all hours column that adds all the days total hours i would like to have my regular hour column take out everything up to 40 hours and then the OT column take everything over 40 hours. is that possible.
I'm interested in the same thing. Did you ever get a response?
Assuming the hours are in column A, you can use the following formulas to extract:
- Regular hours: =IF(A2<=40, A2, "")
- Overtime: =IF(A2>40, A2, "")
The screenshot below shows the result:
If you are looking for something different, please clarify.
Hello everyone,
I´m not sure which function to use to sum the products below in a different table.I tried to use the "sumif" function without sucess.
Products amounts Aples ?????
Aples 1500.00 Oranges ?????
oranges 400.00 Potatoes ?????
potatoes 590.00 Mango ?????
mango 522.00
oranges 789.00
Bananas 632.00
Aples 878.00
potatoes 965.00
Can anyone help me?
Hi Carlos,
The SUMIF function is the right choice. Supposing that column A is the list of products, B is Amounts and C is the product names in the second table (beginning in C2), you can use the following formula:
=SUMIF($A$2:$A$10, C2, $B$2:$B$10)
Hi Svetlana,
Im trying to apply same criteria for different columns (non adjacent), they are working well individually. Error occurs when I tried to add them together with the following formula.
=
SUMIFS(E18:E10000, G18:G10000, "John", I18:I10000, "01/07/2014”) +
SUMIFS(E18:E10000, K18:K10000, "John", M18:M10000, "01/07/2014”) +
SUMIFS(E18:E10000, O18:P10000, "John", Q18:Q10000, "01/07/2014”) +
SUMIFS(E18:E10000, S18:G10000, "John", U18:U10000, "01/07/2014")
Is there another way to add them without having to calculate individually (in different cells) and add. ?
- Ben
There was a typo in my earlier formula.
=
SUMIFS(E18:E10000, G18:G10000, "John", I18:I10000, "01/07/2014”) +
SUMIFS(E18:E10000, K18:K10000, "John", M18:M10000, "01/07/2014”) +
SUMIFS(E18:E10000, O18:O10000, "John", Q18:Q10000, "01/07/2014”) +
SUMIFS(E18:E10000, S18:S10000, "John", U18:U10000, "01/07/2014")
Good post, thanks. I have a followup question. I am currently using this SUMIF($D$3:$D$22,"=1",$B$3:$B$22). Now instead of =1, if I want to provide a cell location it doesnt work. How do I do this? For example, SUMIF($D$3:$D$22,"=K43",$B$3:$B$22)
Any idea on this? When I try this it calculates incorrectly. Do I need to use quotes differently?
Hello Arun R,
I had the same problem and solved it as per the below change.
SUMIF($D$3:$D$22,"="&K43,$B$3:$B$22)
Hope it works for you too!
Any Body can help me to calcuate this
in A column have product name
in B have its price
in C column have mention its quantity
that database is to 13 rows
i want grand total of B multipal with C of each row.
Thank You
Hello Abdul,
Please use the SUMPRODUCT function:
=sumproduct(B2:B13,C2:C13)
You can find more information about SUMPRODUCT here:
https://support.office.microsoft.com/en-us/article/SUMPRODUCT-function-57a7bfa7-f74d-4ead-8c93-57f759c8f616
Hello Abdul,
=SUMPRODUCT(B2:B13,C2:C13)
I am new to SUMIFS functions but seem to be following the formula but get incorrect result.
Using 2 worksheets in the spreadsheet.
First Worksheet - multiple entries for each contract
Column A contract number
Column B Date
Column C Costs (Amount)
second worksheet - one entry for each contract
Column A contract number
Column B Date
So in the 2nd worksheet I want to sum all costs (on 1st worksheet) for each contract that occured after a certain date.
So I have tried the following forumla
SUMIFS(worksheet1$C$1:$C$2000),worksheet1$A$1:$A$20000,worksheet2"$A2",worksheet1$B$1:$B$20000,">=worksheet 1$B2")
When I leave the date criteria off I get the correct figure for the complete total but when I try to add the date criteria I get a value of 0.
Hello Rachel,
Please use this formula:
=SUMIFS(worksheet1!$C$1:$C$2000,worksheet1!$A$1:$A$20000,worksheet2!$A2,worksheet1!$B$1:$B$20000,">="&worksheet1!$B2)
Most likely the issue is due to this part: ">="&worksheet1!$B2
I was having the same problem as Rachel. Alexander's solution worked for me. Stills seems like unnecessarily complicated syntax, but it works.
can i have some examples of SUMIF with IF ??
also COUNTIFS with IF ??
Could you please clarify what examples you need? We have several articles about these functions:
https://www.ablebits.com/office-addins-blog/if-function-excel/
https://www.ablebits.com/office-addins-blog/excel-and-or-xor-not-functions/
https://www.ablebits.com/office-addins-blog/excel-not-equal-to-greater-than-less-than/
https://www.ablebits.com/office-addins-blog/excel-if-function-multiple-conditions/
Dear Sir,
I am looking for to choose a particular rate if for many specific ranges. For example:
0 - 350 Kg = rate 2.5
350 - 500 Kg = rate 1.9
500 - 800 Kg = rate 1.8
800 - Above rate 1.68
So how do i apply this
IF(A10>800,1.68,IF(A10>500,1.8,IF(A10>350,1.9,2.5)))
I need to do something similar to the formula you provided, but instead of:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
I want to do:
=SUM(SUMIF(C2:C9, {A9,A10,A14} , D2:D9))
But it gives me an error. Could you please advise if there is a way to use a cell reference instead of a string or constant?
Thank you!!!
The cell selections will have to be continuous and then you can use array formula for this:
=SUM(SUMIF(C2:C9, A9:A14 , D2:D9))
type the formula and hit ctrl+enter
curly braces will appear around your formula:
{=SUM(SUMIF(C2:C9, A9:A14 , D2:D9))}
I've tried to use this in two different ways but both methods fail to provide the correct answer:
Method 1)
Formula:
=SUM(SUMIFS('Personal History Data Dump'!$J$2:$J$16000,'Personal History Data Dump'!$F$2:$F$16000,'Daily Roster'!$A4,'Personal History Data Dump'!$C$2:$C$16000,Categories!B$4:B$17))
Problem: Only Sums based on the first criteria in Categories!B$4:B$17. Adding "{}" brackets around "Categories!B$4:B$17" only results in an error asking me if I'm not trying to enter a formula into the cell.
Method 2)
Formula:
=SUM(SUMIFS('Personal History Data Dump'!$J$2:$J$16000,'Personal History Data Dump'!$F$2:$F$16000,'Daily Roster'!$A4,'Personal History Data Dump'!$C$2:$C$16000,{Categories!B$4,Categories!B$5,Categories!B$6,Categories!B$7,Categories!B$8,Categories!B$9,Categories!B$10,Categories!B$11,Categories!B$12,Categories!B$13,Categories!B$14,Categories!B$15,Categories!B$16,Categories!B$17}))
Problem: Returns the same error mentioned above, asking me if I'm not trying to enter a formula into the cell. It specifically highlights the first "Categories" reference as an issue.
I've used this exact formula before with success, but instead of referencing cells I inserted specific values between the "{}" brackets. For example, the portion in the brackets looked like this: {">5000","<20000"}.
Is Excel just not recognizing the cell references as cell references? If so, is there any way to get around that?
I want indirect to reference a cell E3 containing {"Cherry","Tomatoes"}
that can be used in a sumproduct+sumif formula like the one below.
=sumproduct(sumif(c2:c10,D2:d10,indirect("E3")))
hi K, did you ever figure this out? i want to do the same.
Did you figure this out? That's exactly what I'm trying to do... I want to reference the array "text" in a separate cell from my formula to make it easier to update, or so I could choose to use a single value instead of an array. Is it possible?
hi, i also have the same issue, is there a solution where i can refer to the text in few cells or in a single cell seperated with ","
thanks
Any buddy has used "SUMIFS" command with "CONTAIN"
you can use the following to simulate contain:
"*"&"whatever you think it needs to contain"&"*"
Adding the "*"& or &"*" allow excel to look for any for any wildcards in front or after the string you want.
Hello Svetlana,
Please help me. How to use Sumif() function, when RANGE (f.e. 4042-5201) and CRITERIA (f.e. 4042) does not equal to each other? In the CRITERIA I have 4042 and need to SUM figures in the RANGE with value 4042-5201.
Thanks in Advance.
Beso
For your Numbers:
=SUMIFS(sum_range;criteria_range1;">=4042";criteria_range1;"="&G4;$B$4:B32;"<"&G5)
For your Numbers:
=SUMIFS(sum_range;criteria_range1;">=4042";criteria_range1;"="&G4;criteria_range1;"<"&G5)
Hope this is helpefull to someone ☺
Last try If my entire post is not showing now, i will give up
For your Numbers:
=SUMIFS(sum_range;criteria_range1;">=4042";criteria_range1;"="&G4;criteria_range1;"<"&G5)
I Hope this is helpefull to someone ☺
Hi Svetlana,
I made a mistake in asking my question! The SUMIFS formula you gave is for cherries AND john. If I want cherries OR john (OR function between column A and B). I am working on a spreadsheet that has a SUM(SUMIFS...) I have multiple columns that have overlapping data, so instead of using AND logic I need to use OR logic between the columns themselves.
Thanks
Got it! Then you can add up several SUMIF fucntions, like this:
=SUMIF(A:A, "cherries", C:C) + SUMIF(B:B, "john", C:C)
that is not correct
=SUMIF(A:A,"cherries",C:C)+SUMIFS(C:C,B:B,"john",A:A,"cherries")
or you could do:
=SUMIF(A:A, "cherries", C:C) + SUMIF(B:B, "john", C:C) - SUMIFS(C:C,B:B,"john",A:A,"cherries")
Algebra has several solutions here that are all equivalent. The solution above mine is actually slightly shorter in terms of formula.
IF you want to total how many cherries were supplied by John (OR function between columns A & B instead of OR function between values in a given column) how could you organize the forumula?
Hello Becca,
In Excel 2007-2013, you can use the following SUMIFS formula (column C is Qty., column A - product and column B - supplier):
=SUMIFS(C:C, A:A, "cherries", B:B, "john")
In earlier Excel versions, you will have to use an array SUM instead (remember to press Ctrl +Shift + Alt):
=SUM((A2:A9="cherries") * ( B2:B9="john") * ( C2:C9))
I'm trying this formula but its not working on my sheet
=SUMIFS(I10:I88,G10:G88,"AGO - PETROL",H10:H88,"874")
My table has all the columns but when pressing enter key to get the
result comes a #value . Can anyone help me knowing the problem?
Instead of SUMIFS you should use SUMIF in this case