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 20. Total comments: 675
Anyone can help, so I have on same sheet 12 different data filled tables for 12 months, on 13th table I would like to see cumulative sum of desired data. I woould like to choose to sum between february and june for example. Table is generated like 5X4 for every month where you can input data how many fruits (apples, bannanas, pears...) I have in different storages (1,2,3...) and would like to know how many fruits I have had between february and june. Hope someone understands and can help. Tnx
Excellent examples and explanations - congratulations on your expertise
This was a very useful article and was just what I was after - thank you!
Quick question, how can I use wildcards for the "Excel SUMIFS with multiple OR criteria" section please?
For example, if column C (Supplier) had a surname as well in the same cell, yet we still wanted to continue searching for just for forename, how would we do this using the formula from "Example 3. SUMPRODUCT & SUMIFS". I tried the following but it didn't work:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, "*"&H1:H3&"*",0))), D2:D9)
Thanks.
Hello Svetlana,
Your explanation vis a vis the use of the Sumifs function was clear, straightforward, and immeasurably useful. I've been attempting to devise a method by which to accomplish the task that the Sumifs function can fulfill for the better part of three days (an attempt which led off in some pretty instructive, but ultimately futile directions). You have my sincere thanks for putting together a tutorial that I've little doubt required a fair degree of time and effort (not to mention the time that went into becoming an expert in the material in the first place). I can move forward with my project thanks to your work. Once more, thank you.
I WANT TO A FORMULA THAT HELP ME FOR CREATING BANK STATEMENT IN EXCEL SHEET .
FOR EXAMPLE:-THAT FORMULA ADD CREDIT SIDE WITH BANK BALANCE AND SUBTRACT DEBIT SIDE WITH AVAILABLE BANK BALANCE AUTOMATICALLY.
I need to count text within multiple tables from the start of the year to TODAY. The tables being each month and the text being vacation days "V" in each cell. I'm having trouble defining the date in the range. The each cell in the top row has the following format:
=TEXT(WEEKDAY(DATE(CalendarYear,3,9),1),"aaa")
The first column is the employees name.
I found the SUMIF function for adding using the date criteria, and the COUNTIF function the count the text "V". I'm having trouble combining the two.
Thanks
Thank you so much for your wonderful, clear easy to follow tutorials :) I have learnt so much and am amazed at how quickly I have done that!!!
How to automate the below process?
Parameter 2
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10
Sales 10 10 10 10 5 5 5 5 5 5
WOC 20 20 20 15 10 10 10 10 10 5
if I vary parameter=4, woc on Wk1=40(consider first weeks sum)
Wk2=35
Please help
I want a formula that will add up all the numbers but only if it has a B. What would be the best formula for that? I would like to and the number and letter all in once cell so it would look something like this.
7B
6B
1R
I have created the following array formula which works fine to summ based on the multiple GL accounts. Now i want to add another criteria in to the formula to only sum based on months. My data is in a list from and it is for 12 months. The idea is that formula will look at the header for name month and than only sum relevant dollars in to the column.
Col H = dollars, Col D = GL account, Col A = Month
=SUM(SUMIFS('GL Data'!$H:$H,'GL Data'!$D:$D,{"400000","400550","430260","400500","403000","403010","403505","404500"}))
Please help. Rashid
I'm looking for a formula that tell me how many time it replaced the same part in different cars. see chart
Car # Replaced Parts
Car 1 Battery
Car 5 Gas Tank
Car 3 Windsheild
Car 4 Battery
Car 5 Gas Tank
Car 2 Battery
Car 4 Lock
Car 1 Filter
Car 1 Battery
Car 5 Windsheild
Car 3 Gas Tank
I replace the Gas Tank 3 time in different (2) cars. But I want to know how many times I replace the Gas Tank in different cars.
Thanks in advance.
CAN YOU HELP WITH A FORMULA TO DETERMINE THE NUMBER OF TIMES "NO SHOW OR CX" APPEARS IN RELATION TO A CELL? FOR EXAMPLE TEXT IN CELLS L40:L150 SPLIT
CELLS N40:N150 WOULD HAVE EITHER CX OR NOW SHOW LISTED CAN I GET A FORMULA INDICATING A TOTAL OF NO SHOW AND CX WITHOUT ADDING ANOTHER COLUMN?
THIS IS WHAT i HAVE TRIED BUT CANNOT GET IT TO WORK:
=COUNTIFS(L40:L150,"SPLIT",N40:N150,"NO SHOW",N40:N150,"CX")
if there is a error cell in the sum range then how we will use this formula
M5 is Starting Date and I5 is Ending date
=SUMIFS(SAmount,SItmName,H8,SaleDate,">=M5",SaleDate,"<=I5")
I am writing sumifs formula in that way but it did not calculate, Please one who help me regarding to this
Thanks
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!
i think i'm over thinking it but I can not get the either or value to function
column g has Yes or no in column h i want it to look at column g and if it is yes populate $250 if it is no populate $150. =IF(G41="NO",150,)*OR(G41="yes",250)
the yes is giving a $0.0 result.
What am I doing wrong?
Help!
Column F = Account Coding Letters ( I will input data into this resembling an account code e.g 001)
Column G = Production coding Letters (I will input data into this resembling initials for someones name e.g PH)
Column H = Net Price of what is spent (e.g £25
Column K = NEEDS TO BE ABLE TO INPUT ANY CODING LETTER FROM EITHER COLUMN F OR G
Column L = Needs to take the sum of the net price that is related to the coding letter that is inputed from column K.
For example say if Polly Holloway bought fuel and I have an account code for fuel that is 001 and an initial code for Lauren which is LH)
I need to be able to input into column L either of the codes 001 or PH (They will never be input together in the same cell) All I need to know how many times Polly bought fuel on our company and how many times fuel was bought altogether.
So in a sense column K needs to be have a range of columns F & G
At the moment the formula I have in column L is only applicable to Column G.
Please see below:
=SUMIF(G$9:G$44,K23,H$9:H$44)
What is the formula that I can put into Column L so it applies to both Column G & Column F?
Please help!
I am using a formula in Excel spreadsheets that works great but it doesn't work in Excel 2003. Can you tell me what formula I need to use in 2003 - 2007 to get the same result as excel 2010
=SUMIFS(D2:D80,$B$2:$B$80,"Overtime Paid",D2:D80,">0:00")
I am using a formula in Excel spreadsheets that works great but it doesn't work in Excel 2003. Can you tell me what formula I need to use in 2003 - 2007 to get the same result as excel 2010
=SUMIFS(D2:D80,$B$2:$B$80,"Overtime Paid",D2:D80,">0:00")
What if I don't want to sum? What if I am looking at listing the commissions that a salesperson received and for what company they sold to. Let's say that the salesperson has a code - 1, 2, 3 and so on, in column A. The customer name is in column B. I want the formula to read column A to the bottom and return the name of every customer they sold to in another tab in column H. The answer would look like this;
Salesperson Customer
7 Apple, McDonalds, Chevron
Can you tell me what the formula would be for that?
Thanks!
Valerie
Hi,
I am having an issue but not 100% if this solves it or not. It involves trying to have different sums performed based on multiple selection criteria.
The idea is to have 2 criteria which can be selected by dropdown lists. The user simply picks the 2 criteria and then places an "X" in the rows he requires. The pricing calculator then works out a cost.
For example if a certain service (Service A, Service B, Service C) is selected from a dropdown list, and a currency is selected from the second drop down list then perform a certain calculation based on thatcriteria.
In this example, if Service A is selected with currency = "GBP" then it would perform a sumif from the GBP price column for that service. If Service A is selected with currency = USD, it will perform a sumif from the USD price column for that service.
I have it working for when only one criteria is applied but cannot seem to get it for both. In the below formula, column B is where the user puts "x" in and then column K has the GBP cost. The formula needs to use a different column for the USD cost (column L).
=IF(G6="Service A",SUMIF(B21:B117,"x",K21:K117), "")
the above formula should read something like IF G6 =Service A AND G7 = "GBP" then do the sum using column K. IF G6 = Service A AND G7 = "USD" then do the sum but using column L.
If anyone knows how to do this, any help would be appreciated.
Thanks
Stephen
I am trying to calculate more than one quantity but cant find the formula to use. Example in column d I have quantity in column e I have Retail price in column e I would like for the total of all the quanitiy at a total. Please help
Very Helpful Svetlana!
hi Svetlana, this was a great post!
i'm hoping you can help me out on a quick question. you used an array argument in your formula in Example 2 of 'Excel SUMIFS with multiple OR criteria'
my question is, how can i reference an array argument from a cell, instead of typing it in a formula:
i want to reference the array argument {"Mike", "John", "Pete"} from just one cell instead of in a formula as you have in the following:
SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
thanks for any help on this!
This is what I used and it worked, but I'm kind of skeptical.
=SUMPRODUCT(C3:C19,$D3:$D19)
I have column B and C of % weight that correspond with $figure in column D. Column B belongs to partner Arthur and Column B belongs to partner Roxy. Column D has total annual expenses for each category e.g. Travel, Computer Purchase...
How would I construct a formula to look into column B identify the % weight, multiply it to $ figure in Column D and sums up the result. Keep in mind that each % weight in the cell can be different so the formula has to identify the weight and than get the corresponding dollar figure for each cell and sum those up together.
Hello Vince,
I am sorry, it is quite difficult to understand the task without seeing your data. If you can send a small sample workbook to support@ablebits.com, we’ll be able to assist you better.
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.
Hi Svetlana
Column A: Project ref
Column B: Total hours on Project in one day
Column C: Total hours on one project overall
Rows = Days of the week
Column A consists of multiple reference numbers which repeat if a project is worked on for more than one day, but not necessarily on consecutive days. I want to be able to cumulatively add the total hours worked on a project over multiple days using a sumif calculation. For example i would like the total number of hours worked on project ref 222 over several non consecutive days in one month. The project ref range is large and therefore i do not want to specify "222" in the criteria but all instances where a reference number is repeated in the ref column.
Can you please help.
Thanks
Dan
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/
I sold 3 plums, 6 apples and 4 pears on the 6th Jan. How do I total the amount of fruit sold on any given day. So in my example, "How many items of fruit did I sell on the 6th Jan?" The result would be 13, i.e. I sold 13 items of fruit on the 6th Jan.
I wish I could send you the actual excel document but looks tricky to paste it here
Variable Formulae Value
HYTRW 5453
JDGFSW 4356
YREDV 5858
ACFV REEEE+K533EG-JLIULM 14633
MHKYU 6878
NTRGV 5433
SVEDH -HYTRW+NTRGV-+BBCVB-JDGFSW -8031
BDHRYH 3213
K533EG 9078
JLIULM 3335
SGEEG GTGS+BDHRYH 7680
WGTHR 7886
BBCVB 3655
VB CVNN 5444
GTGS 4467
REEEE 8890
I have a problem with excels sumifs function. am trying to add information in Column B that is appearing also in Column A and those in column A have values in Column C.
Suppose there are 1000 entries in column A and you want to add some of them based on a function containing information in column B and find the total. How can I proceed? Example; find sum of AAAA+bbbG-JHFG+THRYR where these are infor in column A but spread across that searching for them can be cumbersome and the total should be based on their individual values in col C and results appearing in column D. Can you help me with this please?
Column A B C D
dsgsg
grhhht
AAAA
FGGGET
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)))
Region Amount
Zone 1 1
Zone 1 2
Zone 1 3
Zone 1 4
Zone 1 5
Zone 1 6
Zone 1 7
Zone 1 8
Zone 1 9
Zone 1 10
Zone 2 1
Zone 2 2
Zone 2 3
Zone 2 4
Zone 2 5
Zone 2 6
Zone 2 7
Zone 2 8
Zone 2 9
Zone 2 10
Zone 2 11
Zone 2 12
Zone 2 13
Zone 2 14
Zone 2 15
Franchisee 1
Franchisee 2
Franchisee 3
Franchisee 4
Franchisee 5
Franchisee 6
Franchisee 7
Franchisee 8
Franchisee 9
Franchisee 10
Franchisee 11
Franchisee 12
Franchisee 13
Franchisee 14
Franchisee 15
Franchisee 16
Franchisee 17
Franchisee 18
Franchisee 19
Franchisee 20
I want Top 5 Sum with Region (Zone 1 - 40, Zone 2 - 65 and Franchisee - 90), Kindly provide the formula fo this
Thank You!!!...
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
i want to make a surgical regt.& next sheet or same page report format to auto generate. Regt cell -Sex, next cell -Ulcer,next cell -debridement than nex cell -Rt.Hand/Rt.foot ctc .
in reporting format- to result if male(sex wise) /rt.hand (operated)/Lt.foot (operated)etc.
please help for it. to make in excel
.
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.
Ablebits is an website an excellent teacher ,for the beginners.
I have learn more than a lot.I want to subscribe to this website for receiving the daily updates to become awesome in microsoft office Professional product.
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