So that I have neglected a little the regular publication of posts.
Today subject is: to add and to count with more than a condition. I have already written on this subject, but my intention today is to give a general concept of the method.
Excel offers two functions to add or to count according to conditions: SUMIF and COUNTIF. These functions accept only one condition and its use is simple.
The question is how to add or to count when several conditions must be fulfilled. Let's see a simple COUNTIF example.
We have a list of names, sex and age, and want to know how many people in the list is older than 30.
As you can see, formula in cell C13 is simple: = COUNTIF(C2:C11, ">30")
But suppose that now we want to know how many male people over 30 years are in the list. Function COUNTIF does not accept two conditions.
The solution is to use array formulas or SUMPRODUCT (you can read this post).
How it works? When evaluating an expression like A1 = B1, Excel can give one of two results: FALSE or TRUE. False is associate with 0 (zero) and TRUE with the 1 (one). When we establish the older than 30 "and" male condition, the word "and" is associate to the multiplication operator ("*"). For example, I have written in the range of cells D2:D11 the formula
= AND(C2>30, B2="male")
Now I have added in range E2:E11 the formula = (C2>30)*(B2="male")
As you can seen, if we add the range E2:E11 we will obtain the result 2, that is the number of people who fulfill the two conditions.
Instead of making the operation in two stages, first to evaluate each member and then to add the results, we can do it in one operation using the function SUMPRODUCT this way:
= SUMPRODUCT(((C2:C11)>30)*1, ((B2:B11)="male")*1)
the multiplication by 1 within the parenthesis is made to turn the values FALSE and TRUE to numerical values (0 and 1).
Another alternative is to use the SUM function in a array formula, that is to say, holding and pressing Control+Shift+Enter at the same time when entering the formula.
The formula is: = {SUM(((C2:C11)>30)*((B2:B11)="male")*1)}
This way we can combine several conditions that must be fulfilled simultaneously.
In the case of conditions like "older than 40 or younger than 30", we will use the operator "+" (addition) instead of "*" (multiplication):
= {SUM((((C2:C11)<30)+((c2:c11)>40))*1)}
If you enjoyed this post add to del.icio.us
Technorati Tags: COUNTIF, SUMPRODUCT, Array Formulas, Countif with more than one condition, Sumif with more than one condition
Categories: Functions&Formulas_, Array Formulas_
15 comments:
this was very helpful. THANK YOU.
This has been of great help. I have been trying for quite some time (almost a year) to get a function to do this. I got DCOUNTA in some website, but that had a lot of things to be done. This one is much simpler and easy to use, especially since I had to use it at many places. Thank you very much for this.
This really helped me a lot. I was in search from last few days and I was trying to use 'and' condition with COUNTIF. But I came to know that COUNTIF doesnot accept more than one condition here only. Thanks a lot.
how to count text with more than one condition in excel. for example, to count column aa1, if columns a = x, b = y, c = z, d = t, e = u.
this works great for more complicated situations. Check it out:
=+(COUNTIF($B$3:$B$349,B352)-(SUMPRODUCT((($S$3:$S$349=""))*1, (($B$3:$B$349)=B352)*1)))
Of course use your own ranges and criteria.
Thanks
Nodir
Great solution on the SUMPRODUCT
Saved me hours
Thanks
thanks! this has come in handy!
futher to your example, how would i go about getting the sum of 'ages' of males over 30? ie 44+34 (excluding nathan as he's listed as a female)
Zac,
using SUMPRODUCT
=SUMPRODUCT((sex="male")*(age>30)*age)
where "sex" represents the range in column B in our example and age column C.
To exclude Nathan (he is suffering from an sexual identity problem) you must add another criteria, name<>"Nathan". The formula will now look like this
=SUMPRODUCT((sex="male")*(age>30)*(name<>"Nathan")*age)
where name is the corresponding range in column A.
thank you so much! for the life of me, i couldnt get it working.
also i was just wondering if you could put up a vlookup or hlookup formula to look up the name of the oldest member?
thanks!
Hi Zac
you can combine MAX with INDEX and MATCH. In our example to retrieve the oldest member in the table you use
=INDEX(A2:A11;MATCH(MAX(C2:C11);C2:C11;0))
We use INDEX with MATCH instead of VLOOKUP because the search field is to the left of the criteria field.
GREAT STUFF MAN!!!
You saved me time and a big headache
Hi - New to this site. This is GREAT STUFF. Seems so logical when you explain it.
Related to queston above "futher to your example, how would i go about getting the sum of 'ages' of males over 30? ie 44+34 (excluding nathan as he's listed as a female)"."
...what if I want two conditions (as above) and I also want to use a filter and only add the visible columns? I've read many posts and can't quite seem to figure this out.
You can filter a list using Autofilter or, better, Advanced Filter and make calculations (like SUM, COUNT or AVERAGE) using the SUBTOTAL function.
If you can read Spanish you could check this post in my Spanish blog.
Thank you so much. This is very helpful.
You r a genious mate
Post a Comment