Excel FAQ

[MrKowz]

I've noticed a lot of users ask the same questions on the boards, and the most searched-for item is by far "countif multiple criteria". So that gave me the thought, what about having a post that users can refer to for questions asked numerous times a day. One that thoroughly, and clearly, explains the formula(s), how they are used, and how they can be tailored to fit the user's data.

Here is my draft write-up of something we might be able to use to alleviate the number of redundant posts regarding counting multiple criteria. Please read it over, and provide feedback. I really want to make a difference on these forums beyond the standard helping, and would be happy to create other write-ups for other questions:

------------------------------------

How to perform a multiple criteria count:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
One of the most asked questions on these forums is how to perform a multiple criteria count. This is a simple solution in Excel 2007 or newer versions, however it poses a difficulty in Excel 2003 and older versions.
<o></o>
Let’s use the following as an example:<o></o>
Data:<o></o>

Excel Workbook
	A	B	C	D
1	Date	Amount	Type	Branch
2	12/1/2010	4000	Deposit	East
3	12/1/2010	6000	Deposit	East
4	12/1/2010	2000	Withdrawl	East
5	12/1/2010	3000	Deposit	West
6	12/2/2010	5000	Withdrawl	East
7	12/2/2010	5000	Withdrawl	West
8	12/3/2010	10000	Deposit	West
9	12/3/2010	4000	Withdrawl	East
...

<o></o>
Problem: We want to count the number of Deposits in the East branch.
<o></o>
In Excel 2007 or newer versions, this is quick and easy since Microsoft added in a new function called COUNTIFS. COUNTIFS has a very simple syntax (and is nearly identical to COUNTIF):

=COUNTIFS(range_1, condition_1, range_2, condition_2, …)<o></o>
So using the COUNTIFS function to solve our problem:

=COUNTIFS(C2:C9,”=Deposit”,D2:D9,”=East”)<o></o>
Or alternatively, since COUNTIFS can support entire column references:

=COUNTIFS(C:C,”=Deposit”,D:D,”=East”)<o></o>
However, in Excel 2003 or older versions, counting multiple criteria becomes a bit of an issue. Without the native support of COUNTIFS, we must use another function in a fashion it was not originally intended. SUMPRODUCT is the function we need to use for this.<o></o>
The SUMPRODUCT function takes two or more arrays, multiplies individual entries together, and sums the results (it sums the products of the arrays, hence the name). But before we can use this, we must first identify our conditions. In our problem, we want to count the number of Deposits in the East branch. If we look at this in a logic sense, we are asking:<o></o>
C2:C9=”Deposit”
D2:D9=”East”<o></o>
If we were to look at the data based on these conditions, it would have TRUE/FALSE for where the criteria is/isn’t met (this is purely for illustrative purposes to show what goes on behind-the-scenes):<o></o>

Excel Workbook
	A	B	C	D
1	Date	Amount	Type	Branch
2	12/1/2010	4000	TRUE	TRUE
3	12/1/2010	6000	TRUE	TRUE
4	12/1/2010	2000	FALSE	TRUE
5	12/1/2010	3000	TRUE	FALSE
6	12/2/2010	5000	FALSE	TRUE
7	12/2/2010	5000	FALSE	FALSE
8	12/3/2010	10000	TRUE	FALSE
9	12/3/2010	4000	FALSE	TRUE
...

<o></o>
Looking at this data, wherever there is a TRUE and TRUE statement, that is where both conditions are met, and should be counted. From here, there are two ways SUMPRODUCT can handle the data.<o></o>

• =SUMPRODUCT((C2:C9=”Deposit”)*(D2:D9=”East”))
• =SUMPRODUCT(--(C2:C9=”Deposit”),--(D2:D9=”East”))

<o></o>
Both formulas will return the desired result (2), but they evaluate in very different ways.<o></o>

• The first formula, =SUMPRODUCT((C2:C9=”Deposit”)*(D2:D9=”East”)), creates one array within the formula, based on the conditions:
  
  The first condition: (C2:C9=”Deposit”) returns the array {TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}
  
  The second condition: (D2:D9=”East”) returns the array
  {TRUE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE}
  
  When multiplied together (as the formula shows), a new array is returned:
  {TRUE*TRUE, TRUE*TRUE, FALSE*TRUE, TRUE*FALSE, FALSE*TRUE, FALSE*FALSE, TRUE*FALSE, FALSE*TRUE}
  
  When we multiply TRUE and FALSE statements together, Excel uses their values of 1 and 0 to evaluate and return a number. So
  TRUE*FALSE = 1*0 = 0
  FALSE*FALSE = 0*0 = 0
  FALSE*TRUE = 0*1 = 0
  TRUE*TRUE = 1*1 = 1
  
  After performing the mathematical operation, we have an array of 1 and 0s:
  {1,1,0,0,0,0,0,0}
  
  Now, SUMPRODUCT can add up the values! 1+1+0+0+0+0+0+0 = 2
  
  
• The second formula, =SUMPRODUCT(--(C2:C9=”Deposit”),--(D2:D9=”East”)), evaluates in a similar fashion, except it uses multiple arrays. It holds some advantages over the example not using the --, if you want to read more in depth on this, please read xlDynamic’s full write-up on SUMPRODUCT which is linked at the bottom of this post.
  
  First, the -- (double unary) must be explained. The -- coerces the conditional statement within the parentheses into a 1 or a 0. By doing this, it creates an array of 1s and 0s. It does this by taking each TRUE/FALSE statement and multiplying it by -1 twice. Since TRUE/FALSE holds values of 1/0 (as shown in explanation 1), we multiply them by -1 twice to convert them to a positive, numerical, value:
  
  --(TRUE) = (-1)(-1)(TRUE) = (-1)(-1) = 1
  --(FALSE) = (-1)(-1)(FALSE) = (-1)(0) = 0
  
  So now, SUMPRODUCT has two arrays to look at:
  
  {1,1,0,1,0,0,1,0} and {1,1,0,0,0,0,0,0}
  
  Following the nature of the SUMPRODUCT formula, it now multiplies these two arrays together and adds those products:
  
  1*1 + 1*1 + 0*0 + 1*0 + 0*0 + 0*0 + 1*0 + 0*0 = 1+1+0+0+0+0+0+0 = 2

<o></o>
Like COUNTIFS, SUMPRODUCT can be expanded to hold many conditions, just keep adding more conditions, following the example:<o></o>
=SUMPRODUCT((Conditional1)*(Conditional2)*(Conditional3)*etc)<o></o>
=SUMRPODUCT(--(Conditional1),--(Conditional2),--(Conditional3),etc)<o></o><o></o>
One thing to note is that unlike COUNTIFS, the SUMPRODUCT function cannot use entire column references. This means that your ranges in the conditions must be defined ranges, one-dimensional, and identical in size. If these are not followed, the formula will produce a #NUM! or #VALUE! Error.

For more examples on how SUMPRODUCT is used and the various ways it can be used to count data, please read xlDynamic's thorough writeup here.

 

[jeffreybrown]

Good Afternoon MrKowz,

Here is it least one reference from the past worth looking at.

http://www.mrexcel.com/forum/showthread.php?t=339643

Great job BTW...

 

[MrKowz]

Good Afternoon MrKowz,

Here is it least one reference from the past worth looking at.

http://www.mrexcel.com/forum/showthread.php?t=339643

Great job BTW...

AHH! I've been beaten to the punch. Didn't know that post existed

 

[diddi]

this sounds suspiciously like the beginnings of an index of commonly requested topics
great to see a start on this project.

slightly off topic: just wondering what the policy on external linking is. i am thinking along the lines of perhaps having some pdf tutorials with screen shots etc. sometimes being able to show dialog boxes and the like can make an explanation so much simpler. i have no commercial interest in this project and do not obtain commercial advantage from internet teaching of any type. only an interested volunteer.

 

[PaddyD]

And I presume you're all too young to remember that this exists:

http://www.mrexcel.com/articles.shtml

 

[schielrn]

And I presume you're all too young to remember that this exists:

http://www.mrexcel.com/articles.shtml

I guess that makes me old since I do remember this. I had an old screen name as I have been here since about 2005, but have no idea what that screen name was.

Its amazing how these type of topics roll around oh about every 1-1.5 years, get a lot of support and backing and then fall to the side of the ride and left behind.

Many people have tried to create wiki's and indexes, which are great, but I would say most of the people coming to this site and not necessarily trying to learn about Excel, they are trying to get finished with what they are currently working on. Yes they may pick up a thing here or there, but from the people I work with and watch, they could really care less, all the capabilities Excel has.

Its great that all these ideas come up, but to be honest, they are just re-creating the wheel of someone before them that had this idea a couple years ago. I'd say a small majority actually want to learn what is going on and most just want to be spoon fed and move on to their next task, which may or may not even involve Excel.

I've probably seen about a dozen how-to/beginner's worksheets that get passed around and are very nice, but they die off as well. People live too much in the now, to look at what they may need to know in the future.

That's just my .02

 

[Peter_SSs]

Didn't know that post existed

Post #51 in that thread would indicate otherwise.
Perhaps you meant "Didn't remember that post existed"?

With so much information in the forum, it is hard to remember or simplify or categorise or find it all easily. I guess that is pretty much why the forum does go on when most of the questions have already been asked and answered.

 

[erik.van.geit]

Post #51 in that thread would indicate otherwise.
Perhaps you meant "Didn't remember that post existed"?

With so much information in the forum, it is hard to remember or simplify or categorise or find it all easily. I guess that is pretty much why the forum does go on when most of the questions have already been asked and answered.

Long time ago, I asked the same question twice with an interval of some months: lost the solution and didn't find it back

 

[snowblizz]

I'd say a small majority actually want to learn what is going on and most just want to be spoon fed and move on to their next task, which may or may not even involve Excel.

Professional cynic as I am I immediately thought very much the same.
Here's the problem I foresee:

OP: I want to do countif but on multiple stuff
Answerer1: Here's a link to how it is done: link
OP: My data looks like this [posts bad ascii "graphic" of data], how do I write the formula?
*OR*
OP: I don't understand that
Answerer1: :facepalm: [writes the new formula given the data] Or [explains sumproducts with an example]

I'm hoping that's not how it turns out but yeah...
I am not convinced it will help those we envisage it helping but it could very well be useful for people of "medium" skill. I do recall having seen people refer to previous posts on the subject and sometimes it works even. After all there's not a world of difference between sending someone to an old thread and referring a FAQ thread. Besides the problem of actually finding it of course.

 

[cornflakegirl]

xld has a very good page on the use of SUMPRODUCT, which I sometimes link to in responses. But as schielrn and snowblizz say, lots of posters don't really care how it works, they just want a solution. So I would generally only use the link if a poster says "Wow, that's a cool formula, how does it work?", or if the original question is along the lines of "My sumproduct doesn't work properly, how do I fix it" and I can use the link to supplement the basic explanation that I give alongside the solution.

It would be great to have a repository of really well-explained answers that we could refer people to (rather than trying to reinvent the wheel in each answer). But I know that we've tried before to collate people's favourite useful threads, or similar, and there are just too many. I find it hard enough to remember what exists amongst my own bookmarks and favourite threads - I don't have time to assess the gems that everyone else has found too!