countif with non-contiguous range

[The Grim Discovery]

Good people

I want to count the number of 'yes' values in Col A. The cells I want to count are non-contiguous so I thought that if I defined them as named range 'ColA' and then in A50 where I want the result to populate "=COUNTIF(ColA, yes)" would work. It doesn't. At least it doesn't the way I'm attempting. Anyone care to lend a hand?

Several large thanks in advance.

[ColA range=A8:13, A15, A17, A19:25, A27:A28, A31:33]

 

[godsaaint]

Any reason why you can't just use "=COUNTIF(A1:A49,"Yes")"?

 

[The Grim Discovery]

Because that will include other 'yes's that I don't want to count ie in cells A14, A16 etc

 

[Gerald Higgins]

Is there any other data you can use, perhaps in a different column, to separate the "Yes"s that you want to count, from the "Yes"s that you don't want ?
If so, then you might be able to use COUNTIFS as opposed to COUNTIF.

 

[Marcelo Branco]

Maybe somnething like this

Create a named range, say, RngList (B2:B7)

	B​
1​	RngList​
2​	A8:A13​
3​	A15​
4​	A17​
5​	A19:A25​
6​	A27:A28​
7​	A31:A33​

Then try this formula
=SUMPRODUCT(COUNTIF(INDIRECT(RngList),"Yes"))

Hope this helps

M.

 

[The Grim Discovery]

Thanks Marcelo - that's really appreciated.

 

[Wad Mabbit]

INDIRECT is volatile, is there an alternative?
At the moment I'm using
=(tblStudentProgress[@[D1 Participation]]="N")+(tblStudentProgress[@D2Participation]="N")+(tblStudentProgress[@D3Participation]="N")+(tblStudentProgress[@D4MSMWHS200]="N")+(tblStudentProgress[@D5Participation]="N")+(tblStudentProgress[@[D6 FSKOCM07]]="N")+(tblStudentProgress[@[D7 Participation]]="N")+(tblStudentProgress[@[D8 FSKDIG03]]="N")+(tblStudentProgress[@[10 FSKRDG05]]="N")+(tblStudentProgress[@[10 FSKLRG05]]="N")+(tblStudentProgress[@[11 FSKLRG09]]="N")+(tblStudentProgress[@[12 Participation]]="N")

Which is the neatest I can do at the moment.

 

[mikerickson]

The argument of COUNTIF must be a contagious range.
To get the result you seek, you need to use COUNTIF(A8:13,"yes") + COUNTIF(A15, "yes")+COUNTIF(A17,"yes") +... +COUNTIF(A31:33,"yes")