Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User cheshire_man
(knowledge is power) Sat 20-Apr-13 22:45:49
Print Post

Excel function CountIfS returning incorrect result


[link to this post]
 
I have an Excel spreadsheet where I wish to count the number of cells in each of 12 columns containing the value 5. All the cells in the relevant columns contain a number from 1 to 5 or are blank. They are all formatted as General.

So I have the formula
=COUNTIFS(G3:G39,5,L3:L39,5,Q3:Q39,5,V3:V39,5,AA3:AA39,5,AF3:AF39,5,AK3:AK39,5,AP3:AP39,5,AU3:AU39,5,AZ3:AZ39,5,BE3:BE39,5,BJ3:BJ39,5)
It doesn't (seem to) make any difference whether the criteria is expressed as 5 or "5" or "=5".

4 of the columns contain an occurrence of the tested value, but the formula returns 0

If I use the formula
=COUNTIFS(L3:L39,5,Q3:Q39,5)
which evaluates 2 of the columns actually containing the value 5 it returns 1. And, I'm sure this is a clue but I'm darned if I can see it, if I delete either of the 5 values the result is 0. So it's clearly testing both columns.

When editing the formula Excel correctly highlights all 12 columns.

Any thoughts?

Edit:

I've just tried changing the formula to summing the results of 12 separate CountIf functions
=COUNTIF(G3:G39,5)+COUNTIF(L3:L39,5)+COUNTIF(Q3:Q39,5)+COUNTIF(V3:V39,5)+COUNTIF(AA3:AA39,5)+COUNTIF(AF3:AF39,5)+COUNTIF(AK3:AK39,5)+COUNTIF(AP3:AP39,5)+COUNTIF(AU3:AU39,5)+COUNTIF(AZ3:AZ39,5)+COUNTIF(BE3:BE39,5)+COUNTIF(BJ3:BJ39,5)
and it correctly gives the result 4.

So have I misunderstood the CountIfS function?

Tony
We have more and more laws, and less and less enforcement

Edited by cheshire_man (Sat 20-Apr-13 22:54:08)

Standard User XRaySpeX
(eat-sleep-adslguide) Sun 21-Apr-13 00:07:36
Print Post

Re: Excel function CountIfS returning incorrect result


[re: cheshire_man] [link to this post]
 
Sorry, don't have this plural COUNTIFS in my Excel. Use the singular COUNTIF; you know it works.

What is the syntax/definition of COUNTIFS?

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 20 Meg WBC
Standard User camieabz
(sensei) Sun 21-Apr-13 10:55:06
Print Post

Re: Excel function CountIfS returning incorrect result


[re: cheshire_man] [link to this post]
 
It seems COUNTIFS is basically an 'AND' function, in that it counts the number of occurrences of a value in the first range, then counts the number of occurrences of another values (or the same value) in the next range, and so on. It stops if it hits a null result for any given range, and reports a zero.

Think of it as "How often do all the conditions match?"

~ Camieabz ~

All Connection Data ~ Some plusnet links

I've forgotten more about broadband than I care to remember.


Register (or login) on our website and you will not see this ad.

Standard User camieabz
(sensei) Sun 21-Apr-13 11:25:47
Print Post

Re: Excel function CountIfS returning incorrect result


[re: cheshire_man] [link to this post]
 
Here's an example of how it might be used.

Obviously, the same result can be achieved by summing all the rows or columns then using COUNTIF to see how often if the desired value exists. It looks like Harry and Paul's jackets are on shoogly pegs. wink

~ Camieabz ~

All Connection Data ~ Some plusnet links

I've forgotten more about broadband than I care to remember.
Standard User cheshire_man
(knowledge is power) Sun 21-Apr-13 14:49:13
Print Post

Re: Excel function CountIfS returning incorrect result


[re: XRaySpeX] [link to this post]
 
Sorry, it's in Excel 2007 on AFAIK.

Tony
We have more and more laws, and less and less enforcement
Standard User cheshire_man
(knowledge is power) Sun 21-Apr-13 14:49:50
Print Post

Re: Excel function CountIfS returning incorrect result


[re: camieabz] [link to this post]
 
In reply to a post by camieabz:
It seems COUNTIFS is basically an 'AND' function, in that it counts the number of occurrences of a value in the first range, then counts the number of occurrences of another values (or the same value) in the next range, and so on. It stops if it hits a null result for any given range, and reports a zero.

Think of it as "How often do all the conditions match?"
That makes a lot of sense. Thank you sir.

Tony
We have more and more laws, and less and less enforcement
  Print Thread

Jump to