Why this COUNTIFS function is not working?

Question

I have a data sheet named 101 which includes student information. The Column C Heading is "Session" having data like 2010-11; 2011-12; 2009-10; 2008-09; 2013

In another sheet, I wrote a COUNTIFS formula to determine how many students are there of the session 2010-11;2011-12 and 2013. The formula looks like this:

=COUNTIFS('101'!C1:C324,"2010-11",'101'!C1:C324,"2011-12",'101'!C1:C324,"2013")

But this is returning zero.

Why?

How to revise the formula?

Help please.

Answer 1

Is there any easy way except the one you mentioned above?

I have not mastered the SUMPRODUCT formula.

If you think that there is no other way, pls explain the  formula you framed.

How many dashes here after the first bracket? in =SUMPRODUCT(--(

Hi,

If you don't like SUMPRODUCT then you can do it like this. With this formula it doesn't matter if 2013 is text or a number it will be counted

=COUNTIF('101'!C1:C324,"2010-11")+COUNTIF('101'!C1:C324,"2011-12")+COUNTIF('101'!C1:C324,"2013")

With the sumproduct formula this ISNUMBER(MATCH bit test every cell in the range for any of the 3 values and it returns an array of TRUE:FALSE etc. SUMPRODUCT can't work with TRUE ot FALSE so we use the -- to coerce TRUE into 1 and FALSE into zero and then sumproduct adds them up.

Answer 2

Hi,

It's not working because a cell in that range cannot simultaneously be all of those values, try it this way

=SUMPRODUCT(--(ISNUMBER(MATCH('101'!C1:C234,{"2010-11","2011-12","2013"},0))))

A works of caution. It will work if these are text values but 2013 may well be a number and you may need to change the formula to this. Note in this version the quotes around 2013 are gone

=SUMPRODUCT(--(ISNUMBER(MATCH('101'!C1:C234,{"2010-11","2011-12",2013},0))))

Answer 3

The double unitary negation (two negative signs ( as in =SUMPRODUCT(--(Test) ,,,) are used to coerse the results of Test from Boolean (FALSE or TRUE) to numeric (0 or 1) so we can do arithmetic on them

Bob Philips website on SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

best wishes

Answer 4

Hi,

 

It's not working because a cell in that range cannot simultaneously be all of those values, try it this way

 

 

=SUMPRODUCT(--(ISNUMBER(MATCH('101'!C1:C234,{"2010-11","2011-12","2013"},0))))

 

 

A works of caution. It will work if these are text values but 2013 may well be a number and you may need to change the formula to this. Note in this version the quotes around 2013 are gone

 

 

=SUMPRODUCT(--(ISNUMBER(MATCH('101'!C1:C234,{"2010-11","2011-12",2013},0))))

The formula is returning wrong result.

I filtered the column and found 323 counts but your formula gives 230

Answer 5

Is there any easy way except the one you mentioned above?

I have not mastered the SUMPRODUCT formula.

If you think that there is no other way, pls explain the  formula you framed.

How many dashes here after the first bracket? in =SUMPRODUCT(--(