Ready to chat data? That's what we're here for
Being able to check that the criteria of data entered in to a cell range is useful function in Excel, but now you are able to determine the criteria in a number of cell ranges. In earlier versions of Excel, you can use the COUNTIF function to check a cell range for a single criteria, but from Excel 2007 onwards, you can use the COUNTIFS function for finding multiple criteria in a spreadsheet; proving that knowing your version of Excel can bring wonders to your data. The COUNTIFS function can be used to find the data that meets selected criteria in two or more cell ranges.
The syntax for this the Countifs multiple criteria function is ,
Countifs(criteria_range1, criteria1, [criteria_range2, criteria2]…)
The criteria in the example given are for sales of cars that have exceeded 10 in any given year. The criteria range 1 is B2 to B10 and the criteria is cars. The criteria range 2 is C2 to C10 and the criteria is greater than ( > ) 10. The function is written as,
=COUNTIFS(B2:B10,”cars”,C2:C10,>10). The returned answer in cell A12 is 3, there are three years that the sales of cars have exceeded 10.
Click on the formula tab on the Excel ribbon and then on the More functions button, from the drop down list choose statistical and then click on Countifs. When the Function arguments dialogue box appears you will have an area to enter the criteria range 1 and the same for the criteria. You will get then get the options for entering multiple criteria.
