To check for the presence of specified values within a range, we can use the AND function along with the COUNTIF function in Microsoft Excel.
COUNTIF: This function is used to count the number of cells within a range which meet a given condition.
For Example:-We have a data table in range A2:A11. We need to find out how many times the states are repeating in this range.
AND:- This function is used to check whether all arguments are true and returns TRUE if all arguments are TRUE. If one argument is false, FALSE is returned.
For example:Cell A1 contains student name, B1 contains 50, and we need to check if the value in cell B1 is more than 40 as well as less than 60.
Lets use the COUNTIF function to check the presence of specified values within the range and the AND function will assist us too.
We have data for certain items in range A2:C6 and the 2ndlist is in Column E.
We need to check the presence of the values in column E in the columns A:C.
Follow the below given steps:-
Note: This is an array function, hence you cannot paste the formula in multiple cells. You will have to paste the formula one by one in each cell.
After pasting the formula in each cell, we get 2 cells with False and the remaining True. 700 has a False value in F4 as 700 does not exist in column C. Pager is also False in cell F8 as Pager does not exist in colum A.
This is the way we can check the presence of the specified values within a range.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.