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 13. Total comments: 675
Hi Gabriel
=IFERROR((Z19/AD19),0)
or
=IF(AA19=0,0,Z19/AA19)
I would do the first one personally. Hope it helps!
Michel, I would just add another column with =IF(G2<100,SUM(G2*250)*H2,H2*G2)
I was hoping someone might give me some insight into how I can combine these two functions:
=SUM(Z19/AD19)
=IF(AD19,Z19/AD19,0)
The SUM is delivering a #DIV/0!
This leads to the IF statement which delivers a "0" so it can be read by some conditional formatting I have listed.
I need the cell to be able to read the SUM and calculate when it can (and it does) but if the division is by "0" and I get the #DIV/0! I need that IF calculation to produce the "0"
Can anyone help please?
Gabriel
Hi
I want to include a SUMIF in a SUMPROUCT formula where a value should change if it is lower than a specific value.
=SUMPRODUCT((G2:G36,H2:H36)/SUMIF(G2:G36,"<100", G2:G36*250))
The above function does not work, however, if value(number) in the "G" row is lower than 100, it should be multiplied by 250.
I cannot get it to work...hope you can help.
Thanks
Hi i have a List of number that i wanted to know which of the SUM(combination) that will match with my predefine value.
Example:
In column A1 i have this list of number
100,110,130,150,160
and i wanted to know which of the summation will get 260
i want to sum non-blanks in a row of data after bypassing first three non-blanks. Means I have to sum from the start of 4th non-blank entries without considering first three non-blanks. please support.
Hello,
please I need to fix formula for:
I have to make 3 flights within 90 days.
in C/D/E I have dates of each flight. I have to know if 3 flights are done within 90 days. and according to last date-3rd date ( if 3 flights are done in 90 days)I have to know expiry date, means + 90
thanks
I have an excel file where I capture the estimated working hours of the employee from the website in Sheet1(employee working hours), I need to capture the estimated and actual hours, whichever has the higher hours will be shown in the cell in sheet2(Utilization sheet).
=SUMIFS('From employee working hours'!P:P,'From employee working hours'!Y:Y,'Utilization sheet'!D5,'From employee working hours'!K:K,'Utilization sheet!$C$1, 'From Dashboard'!U:U,"")
- it shows the estimated working hours, the actual hours is from From employee working hours'!Q:Q
hi How can i convert 10010101 into Aplha-numeric A-01-01-01 where digit at first two places 10=A
please show me how to calculate 8 in a cell + RDO in another cell to equel 16
Hello, need help. I want to calculate the amount payable to driver "A" for the pickups and/or deliveries done between 19:00hrs & 01:00 hour on 08/24/16. In this example he should get $10.
No Pdate Ptime Ddate Dtime Amt$
A 8/24/2016 2:10 8/24/2016 5:40 2.00
A 8/24/2016 5:45 8/24/2016 7:40 3.00
A 8/24/2016 17:58 8/24/2016 19:39 5.00
A 8/24/2016 20:30 8/24/2016 22:01 3.00
A 8/24/2016 23:50 8/25/2016 1:05 2.00
No PickupDt PickupTime Delivery Date Delivery Time Drv($)
A 8/24/2016 2:108/24/2016 5:40 2.00
A 8/24/2016 5:45 8/24/2016 7:40 3.00
A 8/24/2016 17:58 8/24/2016 19:39 5.00
A 8/24/2016 20:30 8/24/2016 22:01 3.00
A 8/24/2016 23:50 8/25/2016 1:05 2.00
I need to calculate the amount payable to person A for date 8/22/16 the pickup time and/or delivery time between 19:00hrs & 01:00hrs
No Pickup Date Pickup Time Delivery Date Delivery Time Drv($)
A8/24/2016 2:10 8/24/2016 5:40 2.00
A 8/24/2016 5:45 8/24/2016 7:40 3.00
A 8/24/2016 17:58 8/24/2016 19:39 5.00
A 8/24/2016 20:30 8/24/2016 22:01 3.00
A 8/24/2016 23:50 8/25/2016 1:05 2.00
give some sumif and countifs sumifs,countifs,avg
avgifs problematic examples
Hi,
Can you plz tell me how to solve below mentioned issue.
=SUMIFS(B23:R23,B26:R26,"PAID",B20:R20,"=1/1/2016")
When I put this formula it is working, but what I want is,to change the month of the date when I drag it to other cells in the same raw. It means, the month has to be changed automatically, January 2016, February 2016, March 2016.
=SUMIFS(B23:R23,B26:R26,"PAID",B20:R20,"=1/1/2016")
=SUMIFS(B23:R23,B26:R26,"PAID",B20:R20,"=1/2/2016")
=SUMIFS(B23:R23,B26:R26,"PAID",B20:R20,"=1/3/2016") like this.
How can I get changed only month automatically in this formula. Because all the time I have to change the month of the date manually. Please assist
Dear Admin User,
Please help me, my problem is mentioned the bellow.
1
1202 Need 12000 =sumifs(a6:a10,">5000",b6:b10,"=1",c6:c10,a4)
5000 1 1203
6000 2 1202
6000 2 1202
5000 1 1203
5000 2 1203
5000 2 1202
5000 1 1202
Thanks,
I want to take a single criteria from one column, take a multiple criteria from another column and then sum the final column if the criteria from the first two are there. I know in your world this is a absolutely simple but I'm struggling. I'm looking for PFV in column C,if we have a PFV in column C then we also need 1,2,5,6,45,46,49 or 50 in column E, I then need the sum of the relevant figures in column F. I have to do this for over 100 separate tabs! Thanks for any help you can give me!
In my column A I've all the first name and column B is the last name. When I try to do a sumifs to add any names in Column A it won't give me any result. However, when I do the same sumifs for column B it give me the correct result. How come they can't search the names in Column A. Please advise. Thanks
Hi Frank,
Sorry, it's not possible to determine the source of the problem without seeing the original data. Try typing some names manually in a new sheet, and then try your formula. If it works, then the problem is with the names in the original worksheet, and if it doesn't, then double check the formula.
#DIV/0!
=AVERAGEIFS(Yarn!O$2:O$10000,Yarn!B$2:B$10000,Sheet7!C$1,Yarn!I$2:I$10000,Sheet7!A$3:A$135,Yarn!J$2:J$10000,Sheet7!B$3:B$135)
help me please
Hello Dear All,
I Use Excel 2007, i want to make
E8:E107= Product (Desktop), G8:G107=Technician (Milon), T8:T107=Total Product Value.
i Need to Show Only milon how mutch Keep from Desktop
Suppliers amount
Peter 128089
Peter 128090
Peter 128091
Peter 128092
Peter 128093
Peter 128094
Peter 128095
Peter 128096
Peter 128097
Peter 128098
Peter 128099
Peter 128100
Peter 128101
John 21654114
John 21654115
John 21654116
John 21654117
John 21654118
John 21654119
John 21654120
John 21654121
John 21654122
John 21654123
John 21654124
John 21654125
John 21654126
John 21654127
John 21654128
John 21654129
John 21654130
Tom 48418
Tom 48418
Tom 48418
Tom 48418
Tom 48418
Tom 48418
Tom 48418
Tom 48418
Tom 48418
Tom 48418
Tom 48418
Can you help me for preparing summary sheet?
there're more than 15 suppliers so I wanted to prepare a summary that all of them come just in one sheet.
How to prepare Monthly Payroll in Excel Sheet ?
help me please now and today if possible.
"i have to award DIVISION I, II, III, IV and 0 to my students who have scored different grades in their subjects either A, B, C, D and F where each carries different POINTS such as A=1, B=2,C=3, D=4 and F=5.And note that the minimum subject to be taken by a student is 7 and the maximum is 10 while others can take 8 or 9 is allowed. As well as award division is equivalent to the following points; for Div I =(1-7)points, Div II = (8-12)points, Div III = (13-24)points, Div IV = (25-33)points, Div 0 = (34-35)points. Note that the awarded Division to any students regards/considers to the first seven subject scored highest others less than 7 subjects awarded as INC - incomplete. Thus core subjects are 7 among the taken ones. plz help which formula or function am to apply to award for my class students now.
thanks and be blessed abundantly by God. Amen.
How do I get the total of all Fruit NOT sold by Mike?
Hi Sheil,
You can use a SUMIF formula similar to this:
=SUMIF(B2:B10, "<>"&"mike", C2:C10)
Where B is the column containing the names and C is the column to sum.
Hi, I am trying to calculate a formula for the following data.
t1 t2 t3 TOTAL
-----------------------------
10 10* 10* 20
Critria:
-In this I'm interested in sum of only * marks
like in this total of 3 tests is 20 because there two stars of 10 marks.
Can anyone please guide me?
It is Amazing.
I Love it
Hi, I am trying to calculate a formula for the following data.
Name Gender Grade
---- ------ -----
ABC Male A+
DEF Male A
ACD Female A+
ASD Male A
SDF Female B
AED Male A+
LKJ Male A
LOK Female B
I would like to calculate Following things.
1) Count of Male Students whose Grade is A+?
2) Count of FeMale Students whose Grade is A?
I couldn't able to write the excel formula to find the above things.
Can anyone guide me?
Hello,
I am trying to come up with a formula that populates a sum of numbers from a different sheet (data source) - The tricky part is, I need the formula to cover a few different scenarios.
Here are the things i need to cover:
1. I need all of the items in a certain status (located in one column) that have a date range between 6/3/2016 - 6/9/2016 (located in another column)
2. I need all of the items in 3 different status' (located in the same column) that have a date range between 6/3/2016 - 6/9/2016 (located in another column).
The table I am trying to fill is on a different worksheet within the same workbook. I am having difficulty determining what formula set(s) to use to accomplish this. I will need to repeat the process weekly to have a running total - The data source will also be updated weekly, so these numbers need to be active, not static.
Example:
I need the total of all measures (items) that are in status "1-Template", status "2-Configuration", status "2.1-Reconfig" that were completed between 6/3 - 6/9 - I need this number to be populated into a table I created on sheet 2.
Next, I need all of the measures (items) that are in status "6.3-Production" that were completed between 6/3 - 6/9- I need this number to be populated into a table I created on sheet 2.
The range of the columns for status is A2:A5181
The range of date completed is C2:C5181
Any assistance is greatly appreciated! *I doubt anyone will see this immediately, but if possible, I need this today :) )
1 1
4 2
3 1
2 2
11 1
1 2
4 1
3 2
2 1
11 2
1 1
4 2
3 1
2 2
11 1
1 2
4 1
3 2
2 1
11 2
1 1
4 2
3 1
2 2
11 1
1 2
4 1
3 2
2 1
Fist Colum count only 1, 11 number of count and second Colum 1 number of count please h
Hi,
I am trying to calculate a formula that encompasses more that one cell as a criteria(so I don't have to copy the formula 4 times which each cell.
eg I have list of criteria
DASHBORD
A53 England
A54 America
A55 Scotland
A56 Australia
My formula currently is:
=SUMIFS('Master List'!H:H,'Master List'!I:I,"Ticket",'Master List'!BN:BN,Dashboard!A53)
How do I change Dashboard!A53 to include A54, A55 and A56 eg(A54:A56)
We list our jobs on a WIP (work in progress) worksheet. Column E is the total revenue expected on the job. Column K is our actual cost in dollars of the job. Column P is the % of the job completed based on Cost/Revenue. Column U is the amount invoiced to date. Column X needs to tell us, based on the % complete, less previously invoiced amount, what our projected revenue will be. The part I can't seem to get is how do we tell Column X to only calculate up to 100% of Column P?
If costs exceed 100%, it keeps calculating, telling us our projections need to invoice more then what the expected revenue will be. (Obviously we cant charge the customer more just because we didn't control our costs and keep them under 100%. I need it to only calculate up to 100%.
E: Total Revenue for the job(we enter this amt)
K: Job Costs in Dollar Amount (we enter this amt)
P: Calculated % of Work Complete using the formula cost/revenue
U: Amount invoiced to date (a formula calculating previously invoice + current invoicing)
X: should tell us what our projected revenue is but only up to 100% in column P. Should column P go over 100%, we don't want to use the overage.
I have a sheet that shows planned and actual delivered tonnage for a product.
In the A column I have the delivery date, and in the B column, I have the month number ranging from 1 to 12 based on the date in the A column; in the I column, I have the planned tonnage, and in the J column, I have the actual delivered tonnage. The cells in the J column is blank if no delivery have been done yet.
What I am looking for is a way to make a prognosis calculation based on what has actually been delivered so far this month plus the planned tonnage for the deliveries remaining this month.
Right now, I have a hidden column that either shows the corresponding J cell value if it is bigger than 0 and if not, shows the I value. And then does a summation of this hidden column where the B value corresponds to the month number I am calculating.
Is this possible using functions in Excel?
Hi,
Nice thread.
In your example of =SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
my equivalent would be =SUM(SUMIF(C2:C9, {"2A","2B","2C"} , D2:D9))
while 2 is determined dynamically within the same sheet say it is in cell E1 and then A, B and C have to be concatenated.
I have tried something like below -
=SUM(SUMIF(C2:C9, {"&E1A","&E1B","&E1C"} , D2:D9))
or
=SUM(SUMIF(C2:C9, {"&E1&"A"","&E1"&B"","&E1"&C""} , D2:D9))
Can you please help?
Is it possible to use sumifs to add numbers based on the letter and the date? For example: the sum of all A for 30-Apr (=6)
29-Feb 31-Mar 30-Apr 31-May
A 1 2 3 4
B 1 2 3 4
C 1 2 3 4
A 1 2 3 4
E 1 2 3 4
C 1 2 3 4
G 1 2 3 4
H 1 2 3 4
This is a great article with a bunch of new tricks I do not know about. My question is that when I used the array argument in a SUMIFS function, the results are calculated from only applying the first argument in the list, and ignored the rest. In your example, when I used {1, 2, 3} as the array argument, the SUMIFS function only returns the calculation based on 1, not including 2 or 3. My excel is from Microsoft Office Professional Plus 2013. When I tried to search "array argument" in Excel Help (clicking the "?" on the upper right corner, there was no relevant entry appeared.
Does this array argument a newer feature than Office 2013 or an older feature but eliminated from Office 2013?
Thanks for your help!
If I want to add up the total spent in a budget heading (a drop down list) for a particular month across sheets why is:
=SUMIFS(Sheet2!D2:D11,Sheet2!A2:A11,"Marketing",Sheet2!C2:C11,"Sep") not working when D is amount spent, A is budget heading and C is date
Thank you
Hello. I've tried to use "sumifs" and "sumproduct" functions, but
seems doing something.
Spreedsheet 1: Column A= dates of one month, column B=list of products on each day of a given month, column C=qty;
Spreedsheet 2: Column A=dates of one month, Column B = need to get sum of a particular items from sheet1 column B, of course taking in account date of selling.
Thank you very much in advance.
Hello,
SUMIF or SUMIFS function add the values specified in 'sum_range'. Usually 'sum_range' should be a row or column of values.
What if the 'sum_range' is a table of array. I mean if quantity of fruits supplied every day is tabled, I want to add quantity of apples supplied in a calendar week, for example CW18. Here 'sum_range' would be "C1:I9". Will It possible to have simple syntax for such summation?
Hi, I have a formula
SUM(SUMIFS('File Name'!D:D,'File Name'!$C:$C,{131,132,133,138}))
that works beautifully when the source file is open but gives me the #VALUE error when it is closed.
Is there a way to rewrite it so that I use only the SUM and IF functions, hopefully avoiding the error?
thanks dears,
could you please help me with multiple (or) conditions in multiple columns
i.g:
SUMIFS(A1:A10,B1:B10,{"Mike","John"},C1:C10,{"Apple","Orange"},D1:D10,"",...)
is this can work in somehow, I try but its give unreal result (Less than the actual) "it seems like the excel is calculating the second (or) condition based on the filtered data in the first (or) condition.
thanks in advanced :),,
hi,
i want to ask
how about if we use criteria (A1:G1)
Hello,
i want to learn below farmula, kindly teach me in detail and example.
SUM(IF(logical_test,[value_if_true],[value_if_false])
Thanks - was struggling with SUMIF and AND together - this explained it clearly and concisely and my report now does what I want it to do.
I want to sum a range to be identified (ex: I have multiple case Id's in which I need to sum the age of the same case id's). Any find formula available.
Example
Case ID Hold Age
10241993 1342.87
10241993 0.02
10241993 126.38
10241993 0.01
10505177 144.04
10505177 18.83
10505177 117.7
... might be nice to point out that the ranges cannot be in adjacent worksheets within a workbook, but must be on the same sheet as the cell in which the formula is entered. I've been trying for an hour to find out what's wrong with my SUMIFS using the sum range and one of the two criteria ranges on another worksheet in the workbook. Always got a 0. Then I moved it all to the same worksheet and it worked fine. Waste of an hour, though...
Hi Dave,
In fact, the sum range and criteria ranges can be on different worksheets provided that all the ranges are of the same size. I've just tested a simple formula like =SUMIFS(Sheet1!A1:A4, Sheet2!A1:A4, "a", Sheet3!A1:A4, "b") and it worked just fine.
I am trying to obtain a year to date utility total using this sumifs formula.
=SUMIFS(cost,meter,$B7,service,$C7,period,"<=$M$1",year,$I$6)
Where range names are
cost = total invoice
meter = the meter number
service = type of utility (gas, water, electricity)
period = the accounting month January - December
year = 2015 or 2016
Thus, the report contains data for all periods 1 - 12 but I want a year to date for any month end (e.g. March would include periods 1, 2, and 3).
Any ideas would be appreciated.
Thanks
I managed to find a solution to my earlier query. I used the sumproduct function and it works perfectly. Irrespective of this, I'd still be interested to know why the sumifs function gives out an error. Regards Charles
I'm trying to use the sumifs function in the last column of a Table (Table1) with a Header Title "Total". Want I need is to add cells of each row based on part of their header title. The first header title within Table1 is "Country", while the other header titles include an abbrev. of the name of each month in the year (i.e. Jan, Feb, etc.). For instance, the header title of B2 is "No of Ships - Jan", C2 is "No of Ships - Feb" and so on until - Dec. The formula is in the rightmost row with a Header Title = "Total". I want to add the No of Ships from x month to y month (x & y being two numeric values inserted by a user outside of the table. Therefore I first started out by using the following formula: =month(datevalue(right(Table1[[#Headers],[No of Ships - Jan]:[No of Ships - Dec]],3)&"1")) which works fine when pressing F9 as the resulting value = 1,2,3..12. However when I try to incorporate the array into the sumifs function, I get an error. Here's the first part of the formula placed in n3:
=sumifs(b3:m3,month(datevalue(right(Table1[[#Headers],[No of Ships - Jan]:[No of Ships]],3)&"1")),">="&A1),... A1 = 5 (meaning start adding from May). If I can get the first part to work, I would then continue the sumifs with the same criteria but based on B1 value (say = 8) being the last month to take into account (in other words add those cells from May to August only). I'd appreciate your help and guidance. (Note that the above is only a simplified version of Table1, which actually incorporates other Header Titles but do not include the month name). Thanks Charles
DATE A B C
01-01-16 999.9 2548.75 2510
01-01-16 1982.7 522.15 0
01-01-16 440.9 0 54
01-01-16 535.5 294.95 0
02-01-16 274.4 0 19
02-01-16 697.5 313.5 0
02-01-16 690.3 179.25 120
03-01-16 158.16 153.8 0
03-01-16 207.5 0 560
04-01-16 1034.1 354.2 0
04-01-16 0 1165.5 8600
04-01-16 180.3 27 0
04-01-16 329.4 665.1 0
Please any one can help me.. i want to total date wise of "A" , "B" AND "C".
Please suggest me how can i total this data..
Hi MANOJ KUMAR,
You should use the following array formulas to sum the values for date 01-01-16:
to sum the "A":
{=SUM(B1:B10 * (A1:A10 = DATE(2016, 1, 1)))}
to sum the "B":
{=SUM(C1:C10 * (A1:A10 = DATE(2016, 1, 1)))}
to sum the "C":
{=SUM(D1:D10 * (A1:A10 = DATE(2016, 1, 1)))}
where
"DATE" values are in A1:A10
"A" values are in B1:B10
"B" values are in C1:C10
"C" values are in D1:D10
To enter this formula press CTRL+SHIFT+ENTER.
I think the SUMIF is exactly what I need - but, I want to find matches in column 1 and 2 (names for example) and then when there is a match - check the third column for a value and place the sum of the values in the third column. If somebody can help I would SO appreciate it..
Hi Beth,
You can use the array formula to solve this task.
For example:
{=SUM(C2:C10 * (A2:A10 = "Text1") * (B2:B10 = "Text2"))}
where
The values are in C2:C10.
The column 1 names are in A2:A10.
The column 2 names are in B2:B10.
To enter this formula press CTRL+SHIFT+ENTER.
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
Hi Petros,
You should use the following array formula:
{=SUM(B2:B10 * (A2:A10 = "222"))}
where
"Total hours on Project in one day" is in B2:B10
"Project ref" is in A2:A10
To enter this formula press CTRL+SHIFT+ENTER.
Thanks you all.