Seems like there should be a simple formula for this kind of thing! (My second range of cells is also on another sheet, but I can't imagine that would matter.) Maybe that's because the range sizes are not the same? =COUNTIF(range1,range2) I expected the method shown here to work: Since A3 and A4 match something in B1:B3. That's it! Now you know how to use the COUNTIF function in Excel.I am looking for a formula that will count the number of cells in a range (say A1:A5) whose values match any of the values of another range (say B1:B3).Įdit: I am also looking for a formula that doesn't break randomly. Try it yourself and take a look at how the answer changes as your threshold shifts up and down. In the above example, we count the number of months in which SnackWorld had more than 7 customers by counting occurences of a value greater than that found in cell F5 within the range C3:C6. Let's take a look at another example in which we count the number of cells that contain values over a certain threshold: = COUNTIF( C3:C6, '>'& F5) Like SUMIF, COUNTIF also has a shortcut when our criteria contains =: in the formula above, we could replace '='&G4 with a simple G4, like so: = COUNTIF( C3:C7, G4) Note that in this case, we're using the longform version of criteria - '='&G4. Excel looks in cell G4 and sees the value 'Baked goods', so it knows we want to count the number of cells in our given range that contain that phrase. In the above formula, we first specify the range C3:C7 Excel now knows that we want to count the number of occurences of a particular set of values within those cells. Let's apply the formula to the sample spreadsheet shown above. Using the formula is simple: simply specify the range in which you would like to count values, and the criteria against which you'd like to count.

The formula for COUNTIF is as follows: = COUNTIF( range, criteria)

It'll allow us to count the number of occurences of a particular phrase within a range. We could do this manually - which is fine for a small table like this one - but that becomes difficult when we have a table that contains a lot more data. Let's say we want to count up the number of items in the Baked Goods category. Take a look at the spreadsheet below, which lists SnackWorld sales by item and category. The fundamentals of both functions are the same, and our tutorial on SUMIF provides even more practical examples.

If you aren't yet familiar with the SUMIF formula, take a minute to check out our SUMIF tutorial before reading ahead.

Count if then formula in excel