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 12. Total comments: 675
In "Example 2. SUM & SUMIF with an array argument", there is the following combination of functions
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Instead of writing "John","Mike",etc., can we use the cells that they are into? For example,
=SUM(SUMIF(C2:C9, {G3,G2,G4} , D2:D9))
even though the above it's wrong
Can someone help in solving this as this is killing my machine. Some simple formula to run sheet faster
=SUMIFS('Same Day TAT'!$Q:$Q,'Same Day TAT'!$H:$H,Summary!$D9,'Same Day TAT'!$C:$C,Summary!$B$2,'Same Day TAT'!$D:$D,Summary!$B$3,'Same Day TAT'!$E:$E,Summary!$B$4,'Same Day TAT'!$F:$F,Summary!$B$5,'Same Day TAT'!$G:$G,Summary!$B$6,'Same Day TAT'!$L:$L,Summary!F$8)
Sir
I have two sheets , in one sheet there is only one column enlisted certain items and in second sheet there are two columns. In one column there is list of item & in second column, there is list of values corresponding to items in first column.
Sheet 1
Part No
B
A
C
D
Sheet 2
Engine Qty
A 10
B 10
C 20
D 20
E 25
A 15
I want to run sum if in sheet two only for common items between first column in sheet 1 & sheet 2
please guide
Respected Sir,
i want to create some formula for my office use but i am unable so will you please help me?
if C25= 6,00,000
then E29 can calculate 1% of C25 till 350000
Thne E30 can calulate 15% of C25 till +100000
and last E31 calculate 25 % of C25 balance amount (150000)
i need to devolop sum if for tow condition
sales incentive preapartin
eligilbe only if sales (1) and collection shoulbe achived
Awesome and fantastic concepts by the owner who imparted basic and advance concepts lots of thanks !!!!!!!!!!
invoice num doc date amount
20000056 24.01.2017 536
20000057 23.01.2017 765
20000058 24.01.2017 897
20000059 23.01.2017 545
20000060 24.01.2017 7654
20000061 23.01.2017 897
20000062 24.01.2017 987
20000063 23.01.2017 651
20000064 12.12.2016 210
20000065 01.10.2016 610
20000066 01.10.2016 211
20000067 23.01.2016 109
20000068 23.01.2017 187
94000011 23.01.2016 145
94000012 23.01.2017 167
94000013 23.01.2016 198
94000014 23.01.2016 103
94000017 23.01.2016 1765
94000019 23.01.2016 543
94000032 23.01.2016 532
94000033 23.01.2016 346
94000034 23.01.2016 678
I juat want to know that which all invoices I should add to get exact 3000
I am having trouble coming up with a proper formula for this:
This is a list of positions open and when it was filled.
Site Status Created Filled
A FT 1/1/2000 1/2/2000
B FT 1/2/2000 2/1/2000
C P 1/3/2000
A P 1/4/2000
B FT 1/5/2000 2/1/2000
C P 1/6/2000
A FT 1/7/2000 1/4/2000
B P 1/8/2000
C P 1/9/2000
I am trying to write a formula that basically asks, If site = x, then sum 8 for FT and 6 for PT, for each entry if my date in question falls between the created and filled dates. Also if the filled date is blank that means it's still open. So that needs to be included.
My gut tells me it should be an index/match formula (or vlookup) which i've done in the past.
This is the forumla that i have so far =SUMIFS(Hours,Site, "="&B3, Status, "="&D3, Created, ""&C3)
I created a new column that listed the status as a number.
Name Amount 1099 Y/N
A (250) N
A (2,546.58) Y
B 184.50 N
C (3,500.00) Y
B (1,500.00) N
Hi, if I want to sum if 1099 column indicates "Y" by "Name" so that it will populate A with "Y" ...B with "Y" ..so forth. how do i formulate the functions?
I'm trying to avoid sumifs + sumifs + ...
I have multiple criteria in my data table (date, location, type, subtype) that must match. I'd like to be able to have location and type look to a list for criteria rather than hard code, as I need to use similar function 15 or more times and prefer to change the list reference than typing in and hard-coding the names, places as they change.
sumifs(C:C,B:B,list1,A:A,datecell,D:D,sitelist)
Above is oversimplified as I reference 4-5 criteria ranges
it works when I reference 1 list and it seems that either I have a bad list reference of it won't allow criteria to be lists in 2 places.
can you help?
Appreciate your help with the below formula..
Col A Col B
>=10M 10
=10M 30
using the below formula
=SUMIFS(B2:B4,A2:A4,">=10M")
I am getting result 0 but expecting 40...Using Sumifs as there are other criteria as well along with the above formule..
Thanks in advance..
I see some data is missing..
so to add on to above ">=10M" is a static value not an operator..
Hi,
Is it possible to get the value of a specific cell as your criteria1 or criteria2 ? ex. =SUMIFS(T8:T109,R8:R109,"value inside cell AE6",S8:S109,"value inside cell AE7")
thank you very much for your response. :)
thanks
Dear
Greetings..!
I'm trying to calculate SUMIF function but i do not know how to calculate and getting my answer but i would expecting you guys will solve my problem in excel.
i have an excel sheet that contain in Column (A) Date as (1-1-2016 1:00 PM) in Column (B) Names Like (Adam, John, Wiki) in Column (C) an Amount such as 100, 200, 500 in Column (D) Should be My Answer that's are follows
if in Column (A) Date "1-1-2016 to 31-1-2016" and in Column (B) "Adam" then Calculate the amount in Column (C) "100" and show the Addition in Column (D)
i tried this but i'm fail
=SUMIFS(C1:C3,B1:B3,"Adam")+SUMIFS(C1:C3,B1:B3,">=1-01-2016",A1:A3,"<=31-01-2016")
Kindly Help me
Thanks in Advance
=SUM(SUMIFS(C2:C18,B2:B18,"Del",A2:A18,">=1-Jan-17",A2:A18,"<=15-Jan-17"))
Question regarding Cells Contain non empty "alone".....
Sum Values in Cells C2:C10, if the Corresponding cells in Columns A2,A5,A6,A10's are filled in with totals, remaining A3,A4,A7,A9's are filled in with values. Now I want Sum only Filled in with totals and not values and next condition is totals <10000 should come. I tried below formula but all values are coming(A2,A3,A4,A5,A6,A7,A8,A9,A10).
=SUMIFS(C2:C10,A2:A10,"",C2:C10,"<10000")
Please give me some solution for the above, Thanking You
Hi, thanks for your great article, this helped a lot!
Can you please help me to split an array out of the formula into a different cell to make it easier to update?
=SUMPRODUCT(SUMIF('data'!$B:$B,{"Apples","Oranges","Pears"},'data'!$J:$J))
In the above formula, I wish to change it to something like =SUMPRODUCT(SUMIF('data'!$B:$B,C3,'data'!$J:$J)), where C3 contains {"Apples","Oranges","Pears"}.
Is this possible?
Thanks!
In columns A, B & C I have a list of names. All the names are the same for each column just not each column will have a complete list of all the names nor will it be in order. (See below). My question is this: I need a formula in column D that will tell me
1) if a name in column C matches a name in columns A and B to say "good"
2) if the name in column C matches a name in column A but not B to say "scanned"
3) if the name in column C matches a name in column B but not in column A to say "timesheet".
Example:
A B C D
Adriana Alvin Alvin GOOD
Alvin Tony N/A N/A
Anthony Adriana Anthony SCANNED
Sam Sam Tony TIMESHEET
HI,
USING IF CONDITION HOW WE CAN ADD VALUES LIKE
A1 APPLE 03PCS
A2 ORANGE 8PCS
A3 BANANA 2PCS
A4 APPLE 13PCS
A5 APPLE 07PCS
A6 ORANGE 10PCS
A7 BANANA 4PCS
IF I NEED TO CALCULATE ONLY THE QUANTITY OF AN APPLE USING IF CONDITION WHAT WOULD BE THE FORMULA
Hello, I'm a beginner and a teacher who really needs help with creating my formula. I want the formula to report the number in column 1 if the numbers in the column is between 1 - 4 but add column 1 to 2 if column 1 is 5 or more. If column 1 and 2 is greater than 10, I want it to add column 3 otherwise just say the sum of 1 and 2. This goes on for quite some time and so the function often breaks or doesn't work properly. Any tips on how to do this? I tried to write a formula with the if function but it didn't work well. I'd be grateful for any and every tip. Thanks, T.
Hi!
If my understanding of the task is correct, the following formula should work a treat:
=IF(A2<5, A2, IF(A2+B2>10, A2+B2+C2, A2+B2))
please see attached
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?