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 on: How to use Excel SUMIFS and SUMIF with multiple criteria
by Svetlana Cheusheva, updated on
Comments page 7. Total comments: 675
Hi, I am looking for a more compressed way to write this formula.
I have three different Criteria ranges and three different Sum ranges for each criteria.
The formula is copied down from row to row and the criteria changes with each move but the criteria range and the sum range must be fixed, requiring the use of fixed and relational references. This was coppied from cell AF6.
=IF(AK6+(2*(SUMIF($F$6:$F$48,AG6,$A$6:$A$49) + SUMIF($L$6:$L$48,AG6,$M$6:$M$49) + SUMIF($S$6:$S$48,AG6,$N$6:$N$48)))=0,"",AK6+(2*(SUMIF($F$6:$F$48,AG6,$A$6:$A$49) + SUMIF($L$6:$L$48,AG6,$M$6:$M$49) + SUMIF($S$6:$S$48,AG6,$N$6:$N$48))))
Excel would evaluate the farmola =20*10/5*8 return answer
how is the solution if I need 3 criteria and 3 sum like the example I made by combining with & which is actually wrong, but I don't understand how to combine them so that the desired results are achieved: = (SUMIF ($ B5, P $ 3, $ E5: $ I5 )) & (SUMIF ($ C5, Q $ 3, $ J5: $ L5)) & (SUMIF ($ D5, R $ 3, $ M5: $ N5)) and the results are not as I expected, please take a solution, Thank you
Hi, I am wanting to count the number of set (text) entries on certain dates: today / last 7days / last 30days.
I.e - how many “client defers” in column C do I have recorded today / last 7 / last 30 - date recorded in column B, would love someone to help. Thanks
I need to know how many times person × shows a specific ID# and thier costs. Any suggestions? I've gotten it to work with the person # and costs but I'm not sure how to add in their specific ID# and carry it down to the rest of the group. Can I put multiple ID#'s in parenthesis so it finds the one that has that person # and that specific ID#?
I am trying to create a formula that does the following and I cant get the formula to work for all. only one or two of them. but not all of them.
Need a sum for Column J
if Column E = the words Growth or Acquisition
if Column K = the month of July, 2019
So far I have this;
=SUMIFS($J$2:$J$142,$E$2:$E$142,"Growth")+SUMIFS($J$2:$J$142,$E$2:$E$142,"Aquisition")+SUMIFS($J$2:$J$142,$K$2:$K$142,">="&$L$145,$K$2:$K$142,"="&$L$146,$K$2:$K$142,"<="&$M$146))
But its counting the amounts multiple times
I have an updated formula. It is working and not erring out or counting the amounts multiple times however its not catching all amounts.
=SUMIFS($J$2:$J$137,$E$2:$E$137,{"Growth","Acquisition"},$K$2:$K$137,">="&L140,$K$2:$K$137,"<="&M140)
How could I sum two ranges: C20:I20 and C22:I22 using two Criteria ranges: C19:I19 and C21:I21 ... If if any of the cells in the criteria ranges have the criteria "PTO"?
I have tried a few these, but have not been successful:
=SUMIFS(C20:I20, C19:I19,"PTO”) + SUMIFS(C22:I22, C21:I21,”PTO")
=SUMIFS(C20:I20, C19:I19,"PTO”,[C22:I22, C21:I21])
Essentially, I have this working formulas but need to add another Sum_range and Criteria_range to the formula.
Thanks in advance!
I want to make the criteria as dynamic by getting the input from user via slicer. So I tried using sum before sumifs and if I hard code the multiple criteria on the same column it works =sum(sumifs(sum_range, criteria range, {"a","b"}) but if I want to get the input from user via slicer which reference to a cell it doesn't work. =Sum(sumifs(sum_range, criteria range, name_manager).
Name manager to a input which I receive it from user via slicer.
Hi,
can it be used for the Criteria Range the full column for example as I cant get the formula with it. I cant have the exact range as the range is changing from day to day and there is 29000 lines.
No. of months Amount
12 14
20 14
12 9
15 18
25 14
68 18
70 9
Hi Svetlana,
Could you please help me on following,
What is the formula for If i want to calculate total amount in(Column 2) for 12 to 25 months (Column 1) ?
I am greatly appreciate if you could help me on this. Thank you
My congratulations for this excellent article. I already use some criteria for my company.
What I haven't understood is the following: In the formula
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
the MATCH syntax should say 'Take the value H1:H3, put it to C2:C9, with exact match, eg
--(ISNUMBER(MATCH(H1:H3, C2:C9,0))), D2:D9)
But this gives an error. Why?
Again congratulations for your really excellent article!
I ready type sheet. Some problem formula. Which Lesson 1. Help me
For the second example, if i wanted to return multiple Criteria 1 for example Apples and Oranges Pete has supplied how would I do that?
Hi Chris,
You can do that with one of the below formulas:
=SUM(SUMIFS(C2:C9, A2:A9, {"apples","oranges"}, B2:B9,"pete"))
=SUMIFS(C2:C9, A2:A9,"apples", B2:B9, "pete") + SUMIFS(C2:C9, A2:A9,"oranges", B2:B9, "pete")
Thanks for that it was perfectly. although in some instances i have over 20 criteria and calculating is taking quite a long time. is there a faster way to calculate?
This log has really helped me finding something valuable & usefull, which I may not aware about.
Thanks!!!!
I'm trying to figure out a way to make something like this work but I am unable to do so. Please, will someone help me?
A B C
1 Amounts Condition Result
2 amount 1 if C1=text and B2=empty cell, enter A2; if C1=text and B2=not empty cell, enter A2; if C1=numbers and B2=empty cell enter C1+A2; if C1=numbers and B2=not empty cell enter C1-A2;
3 amount 2 if C2=text and B3=empty cell, enter A3; if C2=text and B3=not empty cell, enter A3; if C2=numbers and B3=empty cell enter C2+A3; if C2=numbers and B3=not empty cell enter C2-A3;
4 amount 2 * if C3=text and B4=empty cell, enter A4; if C3=text and B4=not empty cell, enter A4; if C3=numbers and B4=empty cell enter C3+A4; if C3=numbers and B4=not empty cell enter C3-A4;
5 amount 3 h if C4=text and B5=empty cell, enter A5; if C4=text and B5=not empty cell, enter A5; if C4=numbers and B5=empty cell enter C4+A5; if C4=numbers and B5=not empty cell enter C4-A5;
6 amount 4 if C5=text and B6=empty cell, enter A6; if C5=text and B6=not empty cell, enter A6; if C5=numbers and B6=empty cell enter C5+A6; if C5=numbers and B6=not empty cell enter C5-A6;
7 amount 5 6 if C6=text and B7=empty cell, enter A7; if C6=text and B7=not empty cell, enter A7; if C6=numbers and B7=empty cell enter C6+A7; if C6=numbers and B7=not empty cell enter C6-A7;
So the end result would look and work like this:
A B C
1 Amounts Condition Result
2 10 10
3 15 25
4 20 * 25
5 25 yes 25
6 30 55
7 35 6 55
MR Severity Category Ageing in Days
Bhavnagar NSA Logical Alarm-OMC >3 Days Feedback
Surat SA Physical Alarm - BSS >3 Days OMC Logical
Surat SA Physical Alarm - BSS >3 Days OMC Logical
Ahmedabad SA Physical Alarm - BSS >3 Days RING SPLITTING
Ahmedabad SA Physical Alarm - BSS >3 Days RING SPLITTING
Rajkot NSA Logical Alarm-OMC >3 Days OMC Logical
Ahmedabad SA Physical Alarm - BSS >3 Days TXPL
Ahmedabad SA Physical Alarm - BSS >3 Days TXPL
Baroda SA FAN-Alarm >3 Days FAN FAILURE
Rajkot NSA Logical Alarm-OMC >3 Days wip
Rajkot SA Physical Alarm - BSS >3 Days wip
Ahmedabad NSA Logical Alarm-OMC >3 Days TCTS
Ahmedabad NSA Logical Alarm-OMC >3 Days TCTS
Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
Baroda NSA Logical Alarm-OMC >3 Days VBS_LL services
Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
I am using Sumifs formulea for this attached ment in 2nd Sheet and getting corrected summation pls help to resolved this for 2nd sheet
hi,
i have a list of value is about to 100. i want to know the 6 values which will give a sum of a particular value,
ex:
52636
6326
63699
12563
25699
1236
15693
i want to know 6 values in a above values which give total of 165289.
pls give a solution,
thankyoy
Please help me how to use =sumifs formula to fund the sums more thatn 4 crieteria ranges.
Thanks
Hi,
Can you please help, trying to sum if using multiple criteria including a date range. However it is returning 0.00 value. The date range seems to be the issue as when I remove this the other criteria works. Going around in circles and not sure what I am doing wring.
=SUMIFS('Detail Transaction Listing'!$J:$J,'Detail Transaction Listing'!$D:$D,$B$2,'Detail Transaction Listing'!$A:$A,B15,'Detail Transaction Listing'!$H:$H,">="&Sheet1!$B$5,'Detail Transaction Listing'!$H:$H,"
Sheet 1 B5 & C5 is the criteria field.
Really appreciate your help/
I hoped to have finally found the solution, but it regrettably failed.
{=SUM(($B$38:$B$275=B27)*($C$38:$C$275=C27)*($D$38:$D$275=D27)*($H$38:$H$275))}
In this formula B27, C27 and D27 are the criteria. It works well, ONLY when all criteria cells (B27, C27 and D27) are populated
I was hoping that if one or two of the criteria cells is empty, it would still add $H$38:$H$275 based on whatever criteria (B27 and/or C27 and/or D27) is in place. Instead, the result I'm getting is 0 if one of the criteria is empty.
Any ideas how to solve this?
Lot of thanks to to you. I got the perfect formula at the right time. It saves me lots of time. Great.
=SUMIFS(H2:H1000, D2:D1000,"vehari", G2:G1000, "short")
I want to calculate a range...when two conditions are ture..
But this formula is not working.
Kindly help.
How can I get a count of the number of rows returned using the sumifs function?
My current function is
=SUMIFS $B$2:$B$105,$C$2:$C$105,O111,$D$2:$D$105,O115,$E$2:$E$105,O113)
and I would like to know how many rows were returned to get my total that was returned with the sumifs function.
Thanks,
Matt
This tutorial was a massive help!
Easy to understand and follow, and it works!:)
Thanks so much for providing this for the public!
Hi,I need SUMIFS formula with multiple columns reference.column1-type, column2-length adress, column3- lenghcount. now need total count value with each length adress(column2)
Hi, I am trying to add sum of all outstanding payment of each customer in their respective cell who has crossed the due date but having difficulty in implementing as both criteria has to be checked customer name and the date of supply.
can someone please help
Hi, I am trying to add sum of all outstanding payment of each customer in their respective cell who has crossed the due date but having difficulty in implementing as both criteria has to be checked customer name and the date of supply.
can some please help
Sorry people, read cell A2 as follows
=sumif(criteria range,"Apple",sum range)
Hey people can you tell us how to use sumif for the following;
Criteria 1 - Apple
Criteria 2 -Orange
Criteria 3 -Lemon
Criteria 4 -Pineapple
we want to use sum if to total the sum of "Apples" And all others in s single cell.
eg:
In Cell A1
=sumif(criteria range,"Apple",sum range)
In Cell A2
=sumif(criteria range,"Apple",sum range)
this above formula is not working??? to be specific it sometimes work
also tell us can we apply this formula across sheets in the same excel work book?
awaiting your wise response....
I am trying to make work easier for my team by building a formula that adds up data based on two criteria: The month (this is okay). Each month has for to five weeks. I want the formula to add data in each months for all weeks in the month but up to the current week. I did the formula below. It however appears that Excel does not recognise the last argument below which uses reference to a cell:
=SUMIFS('Weekly targets'!$F$14:$BE$14,'Weekly targets'!$F$1:$BE$1,'Summary by Product'!$E$3,'Weekly targets'!$F$3:$BE$3,"<=$C$4")
Hi,
I need help to put this note in this formula.
=IF(AT1350=400000,0,IF(AT1350<=800000,1000,IF(AT1350<=1200000,2000,IF(AT1350<=2400000,(AT1350-1200000)*5%,IF(AT13504800000,(AT1350-4800000)*15%+(300000)))))))
Note: { Provided that where the taxable income exceeds Rs. 800,000/- the Minimum tax payable shall be Rs. 2,000/-
Hello everyone,
Getting to grips with SUM and SUMIF and have been working on a particular problem using:
=SUM(IF(K10="",O10-P10),IF(K10="PGTA3",O10-(P10/2)))
For this means if "PGTA3" is shown in cell K10 then divide P10 by 2 and subtract the result from O10. However, in the cell K10 I would like this to not be limited to "PGTA3" but anything that includes "PGTA" or "GRP" within the cell.
Essentially O10 is a room size, P10 is a number of students but if ""PGTA or "GRP" is in cell K10 that means there is a second group in another row and the number of students present in the room would be divided in half. Cell K10 could say "PGTA12, "PGTA2", "PGTA3", "GRP1", "GRP2" or a combination of PGTA fields (like "PGTA1,PGTA1" or something like ",PGTA1". The presence of a comma is of no interest to me; if a comma were in a cell alone (it could happen) I want that ignored so I don't think a pure wildcard would work (ie if ANY text in cell K10, divide P10 in half). "PGTA*" and "*" and "*PGTA*" doesn't seem to work anyway and doesn't allow for "GRP". Any help very gratefully received! I aced indexing and array formulas this morning so I'm sure I can ace this too. :)
=SUMIFS('Priority & Standard Cost'!$AJ:$AJ,'Priority & Standard Cost'!$B:$B,Sheet3!B3,'Priority & Standard Cost'!$M:$M,">=04-01-2017",'Priority & Standard Cost'!$M:$M,"='Contract Details'!E3",'Priority & Standard Cost'!$M:$M,"<='Contract Details'!F3")
What is the difference in both formulas.
I have just changed the date from cell in which date is mentioned in same format....but result is 0....Why?
This is my formula:
=SUMIF($C$10:$C$30,"*" & $C$7 &"*",D10:D30)
C7=1 just one number, referring to a shift. So it could be 2 or 3.
D Column = Are just values
Any suggestions.
Last formula solved my problem, Tons of thanks.
What expression do I need to use, to do the following for +100 different criteria, that counts up all quantities in the different criteria's.
=SUM(SUMIF(B2:B152, {"Malus"}, D2:D152))
=SUM(SUMIF(B2:B152, {"Pyrus"}, D2:D152))
to display on the worksheet
Malus 25
Pyrus 36
etc etc for +100 different criteria
Its looking more like I am going to have to use VB code
one more time:
=SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “= 07/01/2018”, “<=07/31/2018”)
I am having problems with my SUMIFS. I need to bring the Invoice Amount from two different worksheets over to my main sheet, but I only want them if they match my GL Code and my Monthly Date.
=SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “=07/01/2018”, “<=07/31/2018”)
-or-
=SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date], MonthlyExpensesSummary[July])
+SUMIFS(Other[Check Amount], Other[G/L Code], MonthlyExpensesSummary[@[G/L Code]], Other[InvoiceDate],[July])
But, neither one work. The top one I can at least get in without error, but doesn't give me results.
Can you help??
Thanks for any assistance,
Tracey
That first one has a typo - it is actually like this:
=SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “= 07/01/2018”, “<=07/31/2018”)
Hello,
I am doing multiple calculation and cannot make correct combination of formulas for the following example:
Column1 Column2
4 x
4 x
5
6 x
6 x
7
7
7
I need formula that will conunt criteria from first column without repetition and only for fow that has mark "X" in second column.
For count of first column I used following formula =SUM(IF(FREQUENCY(...), but I cannot cmbinate it with IF second column has "X".
Thanks in advance
Savo
How to calculate Total sales of Google and Costco customer in North East region???
Date Region Sales Rep Customer Product COGS Sales
6/16/2006 NorthEast Jon Google QJV Item $1,835.90 $3,165.34
6/15/2006 NorthEast Jon Amazon.com ZET Item $6,971.50 $9,295.33
6/15/2006 SouthEast Chin Amazon.com ZON Item $5,406.27 $9,163.17
6/15/2006 SouthEast Chin Solar and Wind Inc. JFA Item $736.25 $836.65
6/14/2006 NorthEast Jeri Costco ZET Item $2,065.11 $4,589.14
6/14/2006 NorthEast Troung Sherman Williams LZB Item $2,553.28 $3,754.82
6/14/2006 NorthEast Steven Peet's Coffee EJL Item $2,315.43 $3,405.05
Good day
If you could please advise.
I am trying to do a sumifs using three criteria, but the formula only allows me to add two criteria for evaluation?
Is there a version of excel that allows this, or is there a combination formula that I could use?
Thanking you in advance.
Thank you! Much appreciated help.
{=sum((F:F="")*(A:A<20)*(B:B))}
F = Name , A = Value , B = Value
I use this formula when I want to know the value which is under 20). This is fine. All I wanna know is how can I calculate the value which between 20 to 50. Please help me out.
Svetlana Cheusheva, you are fantastic. This was quite a piece of valuable information. Thanks a lot, you made my day.
I have a person information spreadsheet with about 2000 rows. I need a formula that will total the 'Sex' and 'Race' columns for the gender and race details. Ex. White Males = ?
Black Males = ? White Females = ?. The single criteria in the 'Sex' column will be either M or F. The 'Race' criteria will be a single identifier for the race. This seems to be a common problem but I cannot make it work. Thanks for any help.
Hello, David:
With that many rows of data I would use a Pivot Table.
QUESTION
Rubber Material Qty1 Cushion Gum Qty2
LL215-128 11.5 CUS GUM 225 MM-EB 1.3
LL215-128 11 CUS GUM 200 MM 1.3
60-62-18-P-Mtk 2.5 CUS GUM 200 MM 1.3
NH165-103 3/4 6.6 CUS GUM 165 MM 0.6
FR175-119 6.8 CUS GUM 165 MM 0.7
NH165-103 3/4 6.6 CUS GUM 165 MM 0.7
FR175-119 6.8 CUS GUM 165 MM 0.8
NH165-103 3/4 6.7 CUS GUM 165 MM 0.6
FR175-119 6.8 CUS GUM 165 MM 0.7
NH165-103 3/4 6.8 CUS GUM 165 MM 0.6
NH165-103 3/4 6.7 CUS GUM 165 MM 0.6
LG-215-128-VT 10.2 CUS GUM 225 MM-EB 1.5
LG-215-128-VT 10.1 CUS GUM 225 MM-EB 1.4
LG-215-128-VT 10.1 CUS GUM 225 MM-EB 1.5
SM 220-128 12.4 CUS GUM 225 MM-EB 1.4
SM 240-130 12.4 CUS GUM 225 MM-EB 1.5
SM 230-130 12.7 CUS GUM 225 MM-EB 1.5
SM 230-130 12.7 CUS GUM 215 MM-EB 1.5
SM 220-130 12.4 CUS GUM 225 MM-EB 1.4
SM 230-130 12.8 CUS GUM 215 MM-EB 1.4
SM 230-130 12.7 CUS GUM 225 MM-EB 1.4
SM 230-130 12.7 CUS GUM 215 MM-EB 1.5
Rl 210-127 11.4 CUS GUM 200 MM 1.4
Rl 210-127 11.4 CUS GUM 200 MM 1.5
RESULT: I WANT TO KNOW SUM OF MATERIAL USED WITH HELP OF SUM-IF / OTHERWISE ANY OTHER FORMULA IS THERE.
MATERIAL
60-62-18-P-Mtk
CUS GUM 165 MM
CUS GUM 200 MM
CUS GUM 215 MM-EB
CUS GUM 225 MM-EB
FR175-119
LG-215-128-VT
LL215-128
NH165-103 3/4
Rl 210-127
SM 220-128
SM 220-130
SM 230-130
SM 240-130
i need sumifs function -- my company loaded flour every day 14 trucks for different companeis . so i need each company howmany ton per day? so please help me
Hello,
I am doing a multiple criteria SUMIFS but with one range I'd like it to be an OR. (The works great for multiple if there are only one or two NOTS, but there are several 'not wanted' and only 2 wanted.)
I can do a SUMIFS + SUMIFS but the braces option do not seem to work for a cell reference being used for the criteria:
working:
=SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,"="&DropDowns!$A$28 ) + SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,"="&DropDowns!$A$27 )
but want to do something like this (since all other criteria is the same)
... $H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal"
============================================================
=SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,{"="&DropDowns!$A$28,"="&DropDowns!$A$27} )
is it placement? Does the 'or' criteria need to be first? (the Or function does not work here)
$L$4:$L$503,{"="&DropDowns!$A$28,"="&DropDowns!$A$27}
Is there another sequence for the braces? {"="&DropDowns!$A$28,"="&DropDowns!$A$27}
The weird "="&cell is the only way my system will allow the criteria in that cell to be used maybe since it is a text.
Thank you for any insight.
excel formula for 25 mg*2=50 mg
I would like to sum the Expected Payment column if the date in the Payment Due Date column is a date in 2018. I have tried both sumif and sumproduct, however, I believe that since the columns I am referencing are formulas which generate a $, date, or blank, it is not working.
K13:K36=Expected Payment (is a formula that generates a $ or blank) L13:L36=Payment Due Date (is a formula that generates a date of blank)
If someone has an answer for this, it would be greatly appreciated! :)