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 17. Total comments: 675
in the first chart (product, supplier).. is there a way to know for example the number of total "Apples" if I leave blank in "supplier"? in other words: if i choose a specific product and supplier, i use sumifs. BUT if i want to leave blank in supplier or product, and want to get the total (would be: blank criteria = consider all). how can I do this? thanks!!!
I would like to use the SUMIFS formula but in the criteria I would like to use the "inverse" of a criteria. For example using your example above I would to sum all items that are NOT supplied by John. This is because sometimes to specify all the criteria would be laborious!!
please help me with this
i need a formula that says
ex.
A2 B2 C2 D2
10 4 40% Not ok
if the result of c2 is equal or less than 50% put "ok" otherwise not ok. thank you very much and i will really appreciate your response.
Hi, Please see spreadsheet below. what i want to do is: Calculate the total of apples in column 1 but only if column 2 is yes.
fruit sold
apples yes
banana no
pineapple yes
pear yes
litchi no
total sold total not sold
apples
banana
pineapple
pear
litchi
Hi - I just can't figure out what to do! This is my first time using SUMIFS ... and I can't get the right answer.
Raw Data
Column A - List of Names
Column C - No. of Hours
Column D - Hour Type (Class, Coach, Administrative, etc.)
SUMMARY
Column H - Name Entered
Column J -- Need to use SUMIFS to total all hours (Column C) for the name in (Column A) that equals the name listed in Column H as long as that Hour Type is NOT "Administrative"
Thanks!!!
Hi,
EXPENSES Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Rent 32903 23984 11990 48653 4745 1720 24218 21844 11752 30100 25235 292
Salaries 42648 18557 16997 47318 43743 12539 12315 43353 26527 17536 41008 2324
Power 12778 43341 10086 31906 17087 47991 28095 25891 16000 7763 22037 1359
Fuel 33187 25257 30246 37761 836 16396 47184 46502 8481 28548 20981 43096
I want to know Jan month Salaries, using sumifs function
I have a large spreadsheet and need an array formula to average month end stock and place it as a starting stock for the next month. I have an array formula as follows {=AVERAGE(IF(R5&G5=$R$5:$R546&$G$5:$G546,$Q$5:$Q546))} but I cant get it to average the following {=AVERAGE(IF(R5&G5=$B$5:$B546&$G$5:$G546,$Q$5:$Q546))}
As you can see I am trying to get the formula to average all in column Q only for those cells in Q that have the same value in Column B & G. I get an answer when the same value is in the same column but i.e value needed to be the same is in the same column as the range but not when I need another reference from a difference cell? I hope I've explained myself
Can someone help me with the following?:
Col A Col B
$31.39 8.25%
$97.43 8.25%
$ 5.31 6.25%
What I'm trying to do is to create a formula where if Col B is 8.25% then sum Col A and divide by 1.0825, and if Col B is 6.25% then sum Col A and divide by 1.0625. Please help. Thank you!
Hi,
Can some body help me to sum the top 10 or 15 values with multiple conditions
I have a sheet 1 with 12 tables, 1 for each month, each table has has 2 rows, engineer name and total repairs. sheet 2 (Raw Data) has a list of each repair over the year, I want to take data from this to show on sheet one how many repairs each engineer did each month. So I need a formula that will sum or count where John did 32 repairs in Jan etc, so I guess I need to set criteria to count if John, if greater than 01/01/15 if less than 31/01/15 but I can't, I triend Sumif, sumifs, countif, countifs, sumproduct, vlookup, I just get the #value error, I can get it to count engineers or dates in the entire raw data sheet but not together. Any Help would be appreciated, my formula is, =COUNTIFS('Raw Data'!A2:A1500, "176WS", 'Raw Data'!H2:H1564, ">=01/01/2015", 'Raw Data'!H2:H1564, "<=31/01/2015")
in the row A, we have the names of customer, and B we have their deposit. using Sumif comment we can calculate their individual deposit easily, in onother colomn.
How can I use this comment as same
I need the sum of deposits except to two guyz (ex: ramu and Raju) from that 10 persons list. how can use command
ilike this function
Hi,
I am using a SUMIF formula against two columns (column 1 = customer name, column 2 = fees due), that adds the total fees due for a customer. I would like to be able to have my SUMIF formula in the third column and be able to drag fill the formula down the column without duplicate values appearing for the same customer. What would I need to add to my formula for this happen?
Thanks.
This is very good with understable examples..thanks a ton
Hi,
If I want to add all values in comum Q12:Q500 based on the date on column T12:T500 but also if the text matches to a certain criteria.
I have 30 associates and I want to separate in a date range by criteria.
I have this in my spreadsheet counting all without dates but I need to separate by date and criteria
=SUMIF(S:S,"CRITICAL",Q:Q)
"q" is the total criticals but I want to separate by date range on column "T" which has three different criterias
"Critical"
"Major"
"Minor"
can you help?
Very helpful - thank you!!!!
Hi,
I want use the formula for to get sum of column F:F as against
=SUMIFS($F:$F,$A:$A,$B$4:$B$101,$B:$B,$A4)
Here Critera1 is the range($B$4:$B$10)of anather sheet
not a single value , how can I modify the formula to get the sum
Here Critera1 is the range($B$4:$B$10)of another sheet
Here Critera1 is the range($B$4:$B$10)in of another sheet
Hi,
I want use the formula for to get sum of column F:F as against
=SUMIFS($F:$F,$A:$A,$B$4:$B$101,$B:$B,$A4)
Here Critera1 is the range($B$4:$B$10)
not a single value , how can I modify the formula to get the sum
Thanks & Regards,
Giri
Hi,
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,
=SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,{50861394,50861765,50861767},$C$4:$C$28,I30))
could you please help me to use the range D1:D3 instead of typeing the values like{50861394,50861765,50861767}
Svetlana - this is fantastic. I bought Ablebits a while ago for several of the functions and I love this additional help!
I've been wanting to convert an investment tracking sheet from simple formulas that are time consuming to reproduce each cycle to advanced/array formulas that will do the heavy lifting each time once I give it the new data.
As such - this information, especially the SUM and SUMIF Array Argument, helped a great deal.
However, when I reproduced the array but changed SUM to AVERAGE and SUMIF to AVERAGEIF, it does not produce the correct average result. I know this as I worked it out the old way to make sure. I couldn't find a separate entry on AVERAGEIF/AVERAGEIFS, so I thought I'd ask here!
It also doesn't like it if I do COUNT/COUNTIF, too.
-Bret
Hi Bret,
Thank you for choosing Ablebits add-ins! As for AVERAGEIF/AVERAGEIFS, you read my mind - I'm working on this tutorial at the moment :)
If you can send us (support@ablebits.com) your sample workbook along with the expected result and we will try to figure out a proper formula.
Thank you, Svetlana! I just sent that to you.
Hi can you help me out with one of the figures
I have perticular set of alphabet numbers but each number carrying different colours , i want sort the colours with the diff numbers and required count
I would like to give sumif function for more than two citeria on the same cell
Example
150/48 R.Blue Int.
150/48 Int. T. Blue
150/36 Int. R. Blue
150/48 Int. C. Brown
150/48 R.Blue Int.
150/48 Int. C. Brown
Formular: i want to sum the total of R.Blue for 150/48 and 150/36
=IF(D7="","",SUMIF(Table,D7&"*",kgs)+SUMIF(Table,E7&"*",kgs)+SUMIF(Table,F7&"*",kgs)+SUMIF(Table,C7&"*",kgs))
giving sumif function for more than one citeria on one cell of text.
Regards
I would like to give sumif function for more than two citeria on the same cell
Example
150/48 R.Blue Int. 1
150/48 Int. T. Blue 2
150/36 Int. R. Blue 3 -----result
150/48 Int. C. Brown 4
150/48 R.Blue Int. 5
150/48 Int. C. Brown 6
Formular: i want to sum the total of R.Blue for 150/48 and 150/36
=SUMIF(C4:C9,"*"&150/36&"*"&"*""R.Blue"*"",D4:D9) but no result, but it should be 3
giving sumif function for more than one citeria on one cell of text.
Dear Svetlana,
thank you very much for posting useful information regarding use of SUMIFS function.
Basir
from Afghanistan
I use excel to export my collection data (med billing)and would like a formula to compare the cells to find matching account numbers, add their balances together (collection agency only takes accounts over $10)and give me a total in the cell of my choice. Some accounts range from 1 charge to multiple and it would be easier to look in one box instead of adding 10 lines to see if it's over(or under). I have looked at some of the formulas but nothing seems to fit.
Thank you
Светлана, я просто хочу вам сказать, что у вас прекрасный блог - лучшее из того, что я встречал в Интернет по теме Excel, и еще у вас прекрасный английский.
Спасибо большое! Мне очень приятно что наш блог читают и соотечественники :)
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")
I tried using the formula as show in Example 3, sumproducts & sumifs, but the formula is giving me the sum of everything except what I asked it to sum. Also, it doesn't sum the data on the top row. I'm very puzzled!
Please help.
Thank You,
Hi Richard,
It'd difficult to detect the source of the problem without seeing your data. If you can send your sample workbook with the formula to our support team at support@ablebits.com, we will try to help.
SKU QTY.
LM200 156
LM100 84
LM25 57
LM10 4
EG200 14
EG100 17
EG25 10
EG10 3
DJ200 41
DJ100 2
DJ25 0
CLA200 19
CLA100 98
CLA25 32
EBF200 41
EBF100 36
EBF25 33
ELC25 2
GT200 232
GT100 125
GT25 63
GT10 1
GEg25 42
GJas25 90
GM25 62
GM10 1
GTL100 61
GTL25 56
GTL10 1
LG25 6
GLH25 21
DIG25 0
OMC25 0
SM25 0
CAM25 0
PPM25 0
LR25 0
TinDG100g 0
TinDR100g 0
TinAS100g 0
RITUAL 0
09CB100 0
12CB100 0
Hi.
my question is how can sum value against if name is duplicate in a column.pls suggest.
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, "")
A B C
Leave Type Start Date End Date
A/C 22-Jul-15 24-Jul-15
A/C 01-Aug-15 10-Aug-15
I want to get a total sum(in days) of difference of C-B for each row. How can i achieve that?
What would the formula be for a SUMIF (Column A is the code) (Column B is the Date) (Column C is the Amount) What would the formula be for the amount of a certain code within a date range, say (July)?
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.
I have list of activities and their date of completion. From that coloumn of completion date, I wanted to know the no of activities completed by Today, Tomorrow , This Week and Next Week. I have used this formula"SUM(IF(EDC>=(TODAY()+7-WEEKDAY(TODAY(),2)+1),IF(EDC<(TODAY()+7-WEEKDAY(TODAY(),2)+1+7),1,0),0))" for Next Week "sum". Here, EDC = Expected date of completion.
=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.
Hello sir,
I have faced sum problem to finding Pdb file. actully i have 100000 pdf file but i m looking 500 pdf any Procdure to finding Pdf file. I m always open one by one file this procedure is too long any sort cut to find all 500 file is simple ways .
Hi,
how can my formula automatically adjust to varying column changes,
e.g
= sumifs(sheet!A:A,sheet!B:B,"apple",Sheet!c:c,"Green")
what if the data received kept the same column heading but the columns changed
e.g - after data has changed
= sumifs(sheet!B:B,sheet!C:C,"apple",Sheet!D:D,"Green")
how can I link the formula to the column headings rather than an actual fixed column.
hope this made sense
Hi, stuck and need help.
I have an example of arriving on 4/3 and departing 6/1 which equals a total of 59 nights. I have a date range I want to SUM a value from an arrival of 5/31 and 6/1 so I need a solution to return the 1 in this example as my SUMIFS is summing the column because 59 is in the column. Need help to sum only 1 because of the date range I entered 5/31 to 6/1 when data is 4/3-6/1.
Arrival Departure Nights
3-Apr-15 1-Jun-15 59
Hi,
in a sum(sumif()) with any array, can i point array values to excel cells ?
I WANT TO SOME THAT COLLUM WHOES COLLUM I GAVE STATES YES ONLY IN MY EXCEL SHEETS
PLS HELP ME
I'm curious how to take a variety of Values and see how many combinations of these values I can create of 6 while remaining under or equal a total of $50,000.
How can I perform this in Excel? So lets say I have 55 variables all with different values say ranging from $6,000 to $12,000 and I want to see how many combinations of 6 I can have while remaining under or equal to the value of $50,000.
Fantastic answers - thanks!!
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?
Dear Sir/Madam,
I'm now working in a hydropower devcelopment project. due i'm weak in excel. I would like to sum or count the number of issue(s) occured/opened and corrective action done/completed in each month (monthly basic)(i.e. number of issues opened in Jan, Feb and Mar... & Number of issue closed/completed in Jan, Feb and Mar...)
I tried many ways with SUMIF, SUMPRODUCT and COUNTIF. But i cannot get the resultant.
Looking to hear from you
Best
Svetlana Thank you for your help I have done my financial report !! It's really helpful please keep it go on.
Thank you, it is really helpfull.
Thanx again..
Dear,
the quires is 17562463 how add ":" after two number exp:-17:56:62:46:3
Svetlana Cheusheva, thank you so much for this helpful article!