How to express a set of cells as a range

[Robert Crandal]

The following cells are only important to me: A1, B5, J20 and M6

I would like to use the above four cells with the Excel countif()
function as follows, but this does NOT work:

countif (A1,B5,J20,M6, 0) ' Does NOT work!

This does not work because countif() only accepts 2 parameters,
a range and a criteria.

How can I make this work?? I already tried the following:

countif(Range(A1,B5,J20,M6), 0 )

but that didn't seem to work either.

Please let me know what I'm missing.

thankx

 

[Per Jessen]

Hi

Look at this:

MyVar = WorksheetFunction.CountIf(Range("A1,B5,J20,M6"), 0)

Regards,
Per

 

[john]

Whilst not 100% certain, I think that you will find what you are trying to do
is a weakness in Excel. You can use the functions on ranges that are
contiguous only.

A solution to this is that you add multiple formulas together that have each
of the contiguous ranges separated something like this:

=SUM(COUNTIF(A1,0),COUNTIF(B5,0),COUNTIF(J20,0),COUNTIF(M6,0))

Hope helps

 

[John_John]

Use a new column (say column "N") to insert references to your cells like:

N1 : =A1
N2 : =B5
N3 : =J20
N4 : =M6

Then you can use COUNTIF() for this column ( column "N") to count zero values.

Ο χÏήστης "Robert Crandal" έγγÏαψε: