# How to do a conditional count in noncontiguous cells

I have the following data on a spreadsheet (xlsx) (Excel 2011 14.6.4 for Mac):

``````No  Yes Yes
No  Yes Yes
No  Yes Yes
``````

I am trying to count the number of "Yes" values for just A1 and C1 skipping B1 (I can't just hide/delete the B column since it's used for other formulae).

I've tried the following formula:

``````=COUNTIF((A1,C1),"Yes")
``````

but I'm getting a Value error with no info on what may have gone wrong. `A1:C1` is inappropriate since I need to skip `B1`. Any solutions?

## 1 Answer

Try this

``````=SUM(COUNTIF(A1,"Yes"),COUNTIF(C1,"Yes"))
``````

The Countif() function specifically requires a range of cells. A list of cells is not a range.
Excel Help states,

COUNTIF(range, criteria)
range Required. One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

Other functions, like SUM(), which can use a list will say so in the help. Excel help for SUM() states,

SUM(number1,[number2],...])
The SUM function adds all the numbers that you specify as arguments. Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function.

• Works perfectly. Any idea though why specifying cells separated by a ',' doesn't work though? It does on some versions of Excel on Windows that I've used. – Kaleb Jun 25 '16 at 13:47
• @Kaleb, answer updated. – B540Glenn Jun 25 '16 at 14:17