Here is a little formula trick if you need to sum a range of cells based on multiple conditions.
Assuming you have the starfleet, captain and flight data like this (seriously, what are you doing with excel again?)
In order to findout how many flights James Kirk did on the Amarillo, you can write an array formula like this:
=SUM(($D$3:$D$24)*($B$3:$B$24="Amarillo")*($C$3:$C$24="James Kirk"))
Remember, the above is an array formula. So you need to type it and press CTRL+SHIFT+Enter.
Update: Or you can also use SUMPRODUCT (replace SUM in the above formula with SUMPRODUCT and just hit Enter (Thanks to Simon for teaching me this tip)
As you can guess, column B has ship names, C has captains and D has # of flights.
Using the same logic, you can make a cross tab (or pivot) like the one shown on the right too.
Further Resources:
- Excel quick tips – Fast and fun way to learn microsoft excel
- Excel array formulas – Tutorials and examples
- 8 ways to use SUMIF() and COUNTIF() formulas
- List of startfleet ship names 😛
78 Responses to “Sumif with multiple conditions [quick tip]”
Could you not just use the sumproduct function in the exact same way, yet with more flexibilty as you are avoiding using it as an array?
ie ( grain of salt on table location, please 🙂 ) =SUMPRODUCT(($A$1:$A$7=$F3)*($B$1:$B$7=G$1)*($C$1:$C$7))
I would normally do this with SUMPRODUCT
Is there any advantage to using arrays?
@Simon.. yeah. Very good point. I didnt know SUMPRODUCT could work like this. Donut for you.. 🙂
@Worm... I am not sure if there would be some advantages of using arrays. May be when you combine the sum with some other formulas in a model, array formula might be better than the normal version. But I dont know any good examples of that. May be one of the more experienced readers can tell me.
Hi, I am fairly new to this site and I really thank Chandoo for maintaining this site, but trust me on Sum Product, It is THE best formula that I have ever come across.
For Eg you can replace a PIVOT table using sum product. Yeah I see a lot of frowns, but SUM Product equally works on numbers as well as on text.
For Text, use =sumproduct(--(Range1="abc"),--(Range2="XYZ")) where range1 is the range and abc is the condition in that range
In this, excel would return an array in the 1st range(1s and 0s for matching abc in the Range1), and return an array in the second range(1s and 0s for matching XYZ in the Range2), and voila, you have the data. Just make sure that the start and end points of Range1 and Range2 are same. (Length of both the range should be exactly same or else you get a #VALUE error.
As for Numbers, just remove the 2 hyphens before opening the brackets and remove the condition.... ! 🙂 and additionally, you can mix text and numbers and remove hyphens for any ranges having only numbers(it would return the count of occurence)
Hope you got the point..... I am all for Sum product..
One word of caution though..Since this returns and array for every condition, if might get heavy on the excelsheet.
Hi Chandoo,
In Excel 2007 you can also use the new SUMIFS function to sum with multiple conditions. The only rub is that it isn't backwards compatible like the array or SUMPRODUCT solutions.
You guys are fantastic !!! I'm finding easier each time to work with named ranges !
Here's my little contribution:
Named Ranges (should be dynamic, but....)
Ship $A$2:$A$8
Captain $B$2:$B$8
flights $C$2:$C$8
in F:F
Summary_ship $F$2:$I$2
this 3:3
Summary_Captain $E$3:$E$6
=SUMPRODUCT((Ship=in Summary_ship)*(Captain=this Summary_Captain)*(flights))
data is in range A1:C8, and summary is in E1:I6.
rgds,
Martín
Is this working in 2010?
@Robert
It should be
I like that the 8th starship flight on your list has been pixellated. Is this the secret black-ops flight to Titan, and did the Intergalactic Intelligence Agency pressure you to keep that info under wraps?
Kpil, would you explain what the hyphens are for please? Or is it only to indicate text rather then numbers?
Thank you.
Kapil, would you explain what the hyphens are for please? Or is it only to indicate text rather then numbers?
Thank you.
Cheryl, the output of an expression of the form X=Y, in Excel, is one of the logical values TRUE or FALSE. These can often be evaluated as 1 or 0, but SUMPRODUCT needs a little clue first. Typing in two negative signs provides the clue by turning the logcal values into numerical values (negating and then negating again leaves the values unchanged, but now they're obviously numbers and not Boolean entities).
There is an alternative to two minus signs, and that's the N() function, which evaluates anything inside the brackets as a number. But there's no obvious advantage to using N(), and most Excel users don't bother.
Cheryl : the 2 hypens make sumproduct return an array, and multiple conditions then have multiple arrays...
Once you have the result in arrays then a sumif is performed internally and result is displayed..
I hope this helps...
Rather put it simply, it converts the SUMPRODUCT into an array formula (something similar to CTRL+SHIFT+ENTER thing that many ppl do to convert a formula into array formula... )
For More on Arrays, Chandoo and his wonderful site can explain in a much subtle and simpler way 🙂
Derek: I am sorry, but I did not understand your post. When the two hypens are used, it ask sumproduct to return the output in the form of an array (something similar to CTRL+SHIFT+ENTER). Please correct me if I am wrong, since I use this day in and day out but i never encountered a problem.. 🙂
Derek was correct.
The two 'hyphens' are simply two negative symbols, turning TRUE or FALSE into 1 or 0, and using the * between the arrays in Simon's example does the same thing.
I use the -- method because it looks clearer to me, but both work.
My SUMPRODUCT formulas end up like :
=SUMPRODUCT(--(Section="Management"),--(Salary="Too High"))
the other way is
=SUMPRODUCT((Section="Management")*(Salary="Too High"))
horses for course really...
silly formatting changed two - into --
should be - - 😉
Thanks! very very helpful :0)
@Ken: Thanks, I am aware of the IFS functions in excel 2007. Since they are not backward compatible, I thought recommending them would be unwise.
@Kapil, Worm and Derek.. that is so cool. I have always seen - - being used in formulas and wondered what it meant. Now I know 🙂
@Mathias: Hush... !
@Martin: Very cool.. thanks 🙂
@Cheryl: You are welcome.
i'm sorry if this is a stupid question, but why do you use '$' in the function? Isn't ($D$3:$D$24) the same thing as writing (D3:D24) meaning the "range of values from D3 block to D24 block"? Is it because you are working with arrays?
The $ makes an absolute reference that doesn't change if you copy the formula to another cell.
If you copy a formula with ($D$3:$D$24) from A1 to B1 it stays the same, but if you copy a formula with (D3:D24) it becomes (E3:E24) - this is probably not going to be what you want - although it might be - depends on circumstances (as always ... 🙂 )
This is why using named ranges can be useful as it makes it clearer what is being done.
(IMHO of course...)
I am using arrays in a spreadsheet ,but have found them to be very slow. Will my problems be solved if I replace them with SUMPRODUCT?
oh! thanks! I learned something today!
@Steven... It is true that arrays make your spreadsheet slow. But only if the array formula is applied over a large range or is processing lot of cells. But I am sure even sumproduct will become slow when you paste it over thousands of cells.
My suggestion is use sumproduct if you can. It is cleaner and simpler to use. It is easy to explain and change. If the formulas are dragging your spreadsheet, then you may have to change the calculation options to manual and press F9 before taking a coffee break.
Thanks Chandoo - you have a great site !
Very helpful?
Please solve the following situation in Excel:
A2:A10 is different fruits name
B2:B10 contains either day or night
C2:C10 contains contents in number
Now,
Search "Apple" from A2:A10,
search "Day" from B2:B10 and
sum all the numbers (c2:c10)meeting the above criteria
@Manoj: Are you not able to use sumproduct for the above?
Indeed. Perfect case for SUMPRODUCT
=SUMPRODUCT(--(A2:A10="Apple"),--(B2:B10="Day"),C2:C10)
or
=SUMPRODUCT((A2:A10="Apple")*(B2:B10="Day")*(C2:C10))
Using named ranges would make it even clearer...
[...] Assuming “log_member_names” refers to the member name column and log_weeknum refers to the last column in the timesheet, we can write a simple SUMPRODUCT formula like this: =SUMPRODUCT(--(log_member_names="John Galt"),--(log_weeknums="3")) to calculate the count of activities John Galt has done on week 3. Learn more about using SUM and SUMPRODUCT to calculate sumif with multiple conditions. [...]
I am trying to put together a tool that can can search, column by column,
for a certain background/fill color, using Conditional Formating (Red), and then copy/paste the
value/text in various columns of the same row for each instance of Red
background colored cells, into another worksheet.
Thanks, any help would be greatly appreciated...
GRADO M H
1 X
1 X
2 X
2 X
2 X
1 X
1 X
2 X
4 X
6 X
4 X
2 X
1 X
2 X
6 X
6 X
4 X
quiero saber cuantas mujeres de primero hay, por separado tambien los hombres, cual sera la funcion.
for those who do not understand Spanish, what Arturo meant was that from that list, he wants to know how many people by grade ("grado") and gender (M means Women, H means Men).
Arturo, deberías trabajar en una tabla dinamica con ese rango, poniendo como filas el campo Grado, y como datos la cuenta de Hombres y la cuenta de Mujeres.
(Arturo, you should use a Pivot Table here, with the grade field as rows, and the count of Men as data, as well as the count of Women).
I presume that's the fastest way, I'll look into it tomorrow and let you all know. (It's 12 in the night, my son is crying, and I just got from seeing Iron Man 2, and wanting to go to bed...).
Rgds,
Martin
cell a2:a43 contains extension
cell b2:b43 contains price
cell c2:c43 contains products name
i want formula where i can only enter extension and then it should show contains price with products name
thanks
@Mehboob
Try =INDEX(A2:C43,MATCH(E2,A2:A43,0),3)&" - "&INDEX(A2:C43,MATCH(E2,A2:A43,0),2)
Put your value to lookup in E2 or change the reference
I am trying to edit an array to add another condition. The array is counting events in a specific year, tied to a specific person. I want to add another condition, but the value of the new condition is subject to change. I don't care what the value is, I just want to count the number of times the value occurs. I have a column for this value, and I want to count the number of times a cell in that column has any text. How do I do that? Thank you.
@Rohon, Can you post your formula so we can see what your doing?
THANK YOU!!!
Here is the formula, as is
{=SUM(IF('DH-Starts'!$N$7:'DH-Starts'!$N$511=$F$1,IF('DH-Starts'!$M$7:'DH-Starts'!$M$511="N",IF('DH-Starts'!$H$7:'DH-Starts'!$H$511=B16,1,0))))}
I want it to do all of that, plus go to the sheet of DH-Starts and count the number of entries in Column G, from row 7 through row 511. I'm not concerned with what is actually IN a cell in Column G, I just care that there's SOMETHING in that cell -- does that make sense?
My attempt:
=SUMPRODUCT((Sheet!B57:Sheet!B69="Criteria")*(Sheet!O57:Sheet!O69="Criteria")*(Sheet!E57:Sheet!E69="Critera"),(Sheet!K57:Sheet!K69))+SUMPRODUCT((Sheet!B57:Sheet!B69="Criteria")*(Sheet!O57:Sheet!O69="Criteria")*(Sheet!E57:Sheet!E69="Critera"),(Sheet!K57:Sheet!K69))
I had troubles using *(Sheet!K57:Sheet!K69) and ended up trying ,(Sheet!K57:Sheet!K69) at the end of the formula; this worked for me
Hi
We want a sum of sales details from a file for particular name and particular month. Sales file has name, month and sales volume. we need output in other file for particular name for particular month. sales detail for a particular name in single month might have more than 1 lines. pls provide formulae
@A
Have you looked at using an Advanced Filter?
Couple on questions on the formula above.
1. Why do we need to multiply arrays in sumproduct formula, isn't it the point of using sumproduct?
i.e. why doesn't this work?
SUMPRODUCT(($D$3:$D$24),($B$3:$B$24="Amarillo"),($C$3:$C$24="James Kirk"))
2. I would like to use name ranges for my data cells (so I can copy across a wide range of data)
Let's say Flights = $D$3:$D$24 , I can write:
SUMPRODUCT(Flights*($B$3:$B$24="Amarillo")*($C$3:$C$24="James Kirk"))
But if in my output table I have Flights in cell F3, I thought about:
SUMPRODUCT(Text(F3,)*($B$3:$B$24="Amarillo")*($C$3:$C$24="James Kirk"))
but it doesn't work....
Thanks in advance for you help guys!
@Sylvain, Where Flights is a named range, try SUMPRODUCT(INDIRECT(F3)*($B$3:$B$24=”Amarillo”)*($C$3:$C$24=”James Kirk”))
Thanks!
Wow! I get all of this. Chandoo is awesome and so are all of the contributors. Since joining this site people think I'm an excel genius. Thanks to all of you.
Thanks Guys for the good work. I have a small problem though. I am attempting to put together students' grades with some optional subjects in there. Scenario is; Add 3 compulsory subjects, pick best two sciences out of three, select best humanity out of three, choose one technical subject out of three and pick any one other best performed subject from the remaining science or humanities or technicals. I willgreatly appreciate your help on this matter.
Chandoo - you have provided the answer that I needed after over a full days worth of google searching on how to write a working formula here at work. Thanks for making this easy to follow for normal folk such as myself.
Hi guys,
I trying to do a SUMIF from pivot table. The data are in pivot table, names are in colum and values (positive and negatives) are in row. On another sheet I want to sum if the name match, then add negative numbers in one colum and positive in another colum.
Many thanks
This formula works amazingly well to find all the instances in column A for the multiple criteria in column C and returns the quantities of the column A items that are in column B.
=SUMPRODUCT(SUMIF($A$413:$A$419," * "&$C$413:$C$416&" *", $B$413:$B$419))
How can I get this to work in VBA?
Hi,
I am trying to figure out how to build a formula, I thought SUMIFS would do the trick, but it seems I need an OR operator.
here is what I am trying to do. I have a sheet A, that contains Sales Rep Names and Sales Amount. In sheet B, I have another set of names that I need to pair up with Names on Sheet B and sum amounts. In other words
Sheet A
Alex - Sales $15
Michael - Sales $15
Sheet B
John - - John's amount is a combination of Sales of Alex and Michael.
add Alex + Michael =$25. I have this formula - but it's really stupid, I am not saving myself any time:) SUMIFS(annual,rsds,"alex")+SUMIFS(annual,rsds,"michael")
Thanks,
esta
I think you've got the parameters in the wrong order. It's SUMIF(range,criteria,sum range) - so it's likely to be SUMIF(annual,"alex",rsds)+SUMIF(annual,"michael",rsds)
I want to use an "or" condition within an array within a sumproduct statement.
Example of what I have now:
=sumproduct((a2:a100)*(b2:b100="Atlanta")*(c2:c100>=VALUE("09/01/2011")))
What I want: is to make the following work whenever "Houston" OR "Atlanta" appears in column B, not just Atlanta.
Any suggestions for the syntax?
You need to add the two conditions.
SUMPRODUCT((a2:a100)*((b2:b100=”Atlanta”)+(b2:b100=”Houston”))*(c2:c100>=VALUE(“09/01/2011?)))
You're awesome! Worked like a charm!
Thank you.
I am having difficulty with the sumproduct formula
Here is my formula
=SUMPRODUCT(('2012'!N:N)*(('2012'!K:K="AFE")+('2012'!K:K="AFT"))*('2012'!A:A='YTD summary'!A5))
I have multiply conditions and multiple spreadsheets
What am I doing wrong
You can't use whole column references in SUMPRODUCT. You have to use a full reference. So ’2012?!N:N won't work - you need (for instance) ’2012?!N2:N200 and so on.
I modified the above formula for summing multiple criterias to pull data from one worksheet into another: =SUM(('Parts Usage'!$F$2:$F$1691)*('Parts Usage'!$A$2:$A$1691="Jan-11")*('Parts Usage'!$D$2:$D$1691="MK-2326"))
However, it only returns a value of 0. Any help to correct my formula would be greatly appreciated.
@James
I think your date is incorrect
The Date you have displayed is a Formatted Date and is only showing the Month-Year as Jan-11
where as I think the cells will have real dates
Select several date cells
Change the custom number format to dd mmm yy
if they are all the same probably 1 Jan 11 your formula will need to be:
=SUM((‘Parts Usage’!$F$2:$F$1691)*(‘Parts Usage’!$A$2:$A$1691=”1-Jan-11?)*(‘Parts Usage’!$D$2:$D$1691=”MK-2326?))
or
=SUM((‘Parts Usage’!$F$2:$F$1691)*(‘Parts Usage’!$A$2:$A$1691=Date(2011,1,1))*(‘Parts Usage’!$D$2:$D$1691=”MK-2326?))
or
=SUM((‘Parts Usage’!$F$2:$F$1691)*(‘Parts Usage’!$A$2:$A$1691=40544)*(‘Parts Usage’!$D$2:$D$1691=”MK-2326?))
I will try these. Thanks for your help! I will let you know the outcome.
v/r,
James
I did as you suggested and changed the date format, but I did use d-mmm-yyyy. For some reason it did not like the dd mmm yy. In addition, I replaced the end quotation for MK-2326 and date 1-Jan-11 with a "?" and it still gives me a value of 0.
=SUM(('Parts Usage'!$F$2:$F$1691)*('Parts Usage'!$A$2:$A$1691="1-Jan-2011")*('Parts Usage'!$D$2:$D$1691="MK-2326"))
@James
Can you post your file somewhere or email it to me?
Yes, I can email the file to you. Thanks for you help!
Do I send you an email using the following email address?
chandoo.d @ gmail.com
@James
Click on my Name
The email address is at the bottom of the page
In general when matching dates, I'll match against a cell with a date in it. So I would, for instance, put the date '1 Jan 2011' in cell T1, then that part of the formula just becomes
(‘Parts Usage’!$A$2:$A$1691=$T$1)
and avoids fiddling with formats.
I did do this, but it is still giving me problems. Sorry for not responding sooner to your reply.
Hello
I'm using the SUMIF to tally a a column wiht a -number
what it's doing is, if in column g there is a c then add up what is in column f
but in colum f i have a - but it's still adding not doing a real total.
how can i fix this
this is the formula i'm using
=SUMIF('218710'!G3:G73,"C",'218710'!F3:F73)
Julie
Do you want to include or exclude the negatives?
As I'm pretty sure Excel won't care if the numbers in Column F are +'ve or -'ve
If you want to exclude the negatives as well try:
=SUMIFS('218710'!F3:F73,'218710'!G3:G73,"C",'218710'!F3:F73,">0")(Requires Excel 2007+)
or
=SUMPRODUCT(('218710'!G3:G17="C")*('218710'!F3:F17>0),'218710'!F3:F17)(All Excel versions)
If you copy / paste the above retype the " and ' characters in your formula
Hi, I've been using SUMPRODUCT with dynamic Ranges (as the dataset grows month on month) for a few months and all of a sudden the formulas are returning #N/A...I have no idea why this is. Is this a known issue for SUMPRODUCT?
The formulas have been working fine up until I've just pasted in the new dataset. An example of one of the formulas is as follows:
=SUMPRODUCT((clientgroup="physical disability")*(count="Yes")*(temp?="permanent")*(ageband<>"18-64")*(hometype="local authority staffed")*(remove="no"))
Are all the ranges the same size? They need to be.
I'm trying to find the correct formula and not sure if I need sumif or sumif s. Can anyone give me some advice.
Bowler 1 has a score of 240 and is awarded 2 points
Bowler 2 has a score of 190 and is awarded 1 points
Bowler 3 has a score of 180 and is awarded 0 points
Total points that can be awarded is 3 points.
Problem is what happens if there is a tie?
As of now I have to go in and award the points for this and was trying to get excel to do it for me.
Bowler 1 has a score of 240 and is awarded 1.5 points
Bowler 2 has a score of 240 and is awarded 1.5 points
Bowler 3 has a score of 180 and is awarded 0 points
Below is the break down on how the points are awarded.
Singles: 2 Points for 1st
1 points for 2nd
0 points for 3rd
Tie: 1st place 1.5 points
0 points for 3rd
Doubles: 2 points for each bowler for 1st
1 points for each bowler for 2nd
0 points for each bowler for 3rd
Ties: 1.5 points for each bowler for 1st
0 points for 3rd
I got problem to sumproduct(sumif( for multiple criteria's range.
I got 2 criteria range how to sum them??
SUMPRODUCT(SUMIFS($G$52:$G$7385,$B$52:$B$7385,P47,$O$52:$O$7385,">="&Q47,$O$52:$O$7385,"<="&R47,$L$52:$L$7385,$T$41:$T$46,$I$52:$I$7385,$T$27:$T$40))
What formula would I use in columns Z, AA, AB, AC if I want columns A:U to match then sum columns V, W, X,Y? Then delete the rows that that are duplicates. I am thinking the formula for summing V:Y would be in columns Z:AC.
For example:
Formula for the sum totals would be in the following columns:
V in Z
W in AA
X in AB
Y in AC
Anyone have a suggestion on how to combine SUMPRODUCT and SUMIFS? Example being I would like to SUMPRODUCT the data in Col A and Col B but would like to use SUMIFS with a Criteria of "1", so only SUMPRODUCT Col A & B when Col C has a "1" in the corresponding Row. Any help is appreciated.
Col A Col B Col C
Qty Price Only if = 1
4 $1.00 1
2 $2.00 0
2 $1.50 1
8 $2.00 1
@LearningBAM
Can you post a question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file and give an example of the result you are after
How to find Highest & Lowest "Flights" count using ship as a reference.
Please help me by using formula and not by fliter
@Manju
Can you please post the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a file so that a specific answer can be delivered.
I have difficulty to post my questions http://forum.chandoo.org/ page. Can you please reply here only.
Question 1
I have list of Group Leader name and their agents.
I need to find in which group more defaulters and which group of agents not performing by using formula
Example data
Group name agent name Count of sales
X A 20
X B 5
X C 2
Y S 15
Y T 10
Y U 5
@Manju
You have to register to post questions there
Goto http://forum.chandoo.org/
Goto Ask an Excel Question
Post New thread
Type your question
Attach a file
Please attach a file so that a specific answer can be delivered.
hi , i just wanted to know i coding in VBA but i dont know how to use it as work.