Mystery solved: Find out how to use COUNTIF with non-contiguous cells

by Kara Soos

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

 

Why doesn’t the COUNTIF function work with a non-contiguous range of cells? The COUNT function doesn’t seem to have a problem, but when I try to use a non-contiguous range of cells for the COUNTIF function, I get a #VALUE! error. Can you help?

 

Norman Martens

Architect

San Francisco, Calif.

 

This does bring up a strange problem that I’m sure puzzles many Excel users. And the workaround may seem like it’s from left field, but it works!

The key is to combine the COUNTIF function with the SUM function and the INDIRECT function. Sound crazy? Let’s take a look at an example. Let’s say you want to count the number of orders that total at least $50 in your worksheet, but the ranges you want to include aren’t contiguous, as shown in Figure A. Here’s how to do it.