CountIF, CountIFs
- Thread starter rod411
- Start date
Somendra Misra
Excel Ninja
Hi,
Just another alternative,
=SUM(COUNTIF(Table1[Rdg],{"II D";"II C"}))
Regards,
Just another alternative,
=SUM(COUNTIF(Table1[Rdg],{"II D";"II C"}))
Regards,
NARAYANK991
Excel Ninja
Hi ,
The explanation for why your posted formula does not work is as follows :
The COUNTIF function returns data which satisfies one criterion ; suppose you want to see how many cells in the range Class!D4:D27 have the text II D in them ; you can use the formula :
=COUNTIF(Class!D4:D27,"II D")
Now , if you want to see how many cells in the same range satisfy two criteria , such as II C and II D , it is not really an AND as we have written in English ; it is actually an OR. This is because logically , one cell can only have either II C or II D , and not both i.e. one cell cannot both be II C and II D at the same time.
The COUNTIFS function returns the result of data satisfying two criteria , but doing this at the same time ; for example , we can use the COUNTIFS function to tell us how many numbers in a given range are :
ODD and GREATER THAN 10
ODD and MULTIPLES OF 3
This is because these two criteria are not mutually exclusive , since there can be data which satisfies both the criteria at the same time.
However , we cannot use the COUNTIFS function to tell us how many cells are ZERO and GREATER THAN 0 , since what we mean by the word and is actually OR , since the same cell cannot have a number which is 0 and greater than 0 at the same time.
The solution in cases where the criteria are mutually exclusive is to use two or more COUNTIF statements , depending on how many criteria are being used , or use the array construct that Misra has posted.
Narayan
The explanation for why your posted formula does not work is as follows :
The COUNTIF function returns data which satisfies one criterion ; suppose you want to see how many cells in the range Class!D4:D27 have the text II D in them ; you can use the formula :
=COUNTIF(Class!D4:D27,"II D")
Now , if you want to see how many cells in the same range satisfy two criteria , such as II C and II D , it is not really an AND as we have written in English ; it is actually an OR. This is because logically , one cell can only have either II C or II D , and not both i.e. one cell cannot both be II C and II D at the same time.
The COUNTIFS function returns the result of data satisfying two criteria , but doing this at the same time ; for example , we can use the COUNTIFS function to tell us how many numbers in a given range are :
ODD and GREATER THAN 10
ODD and MULTIPLES OF 3
This is because these two criteria are not mutually exclusive , since there can be data which satisfies both the criteria at the same time.
However , we cannot use the COUNTIFS function to tell us how many cells are ZERO and GREATER THAN 0 , since what we mean by the word and is actually OR , since the same cell cannot have a number which is 0 and greater than 0 at the same time.
The solution in cases where the criteria are mutually exclusive is to use two or more COUNTIF statements , depending on how many criteria are being used , or use the array construct that Misra has posted.
Narayan