COUNTIF with non-contiguous Range 3

[PeterMoran]

Hi,

I think I'm having a bad day!

I want to specify a COUNTIF as follows:

=COUNTIF((D3,D6,D9,D12),5)

All I get is a "#VALUE"!

I even tried defining the same cells as a Range - ABC, but the same result!

Suggestions please?

Peter Moran

 

[SkipVought]

hi,

So does it matter what's between? What's wrong with
[tt]
=COUNTIF(D312,5)
[/tt]
Or how about this?
[tt]
=MOD(ROW(D312)+1,3)*(D312)
[/tt]
entered as an ARRAY (alt+cntr+enter)



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PeterMoran]

Hi Skip,

Thanks for your prompt reply.

It's the old story - someone else's spreadsheet they want improved!

Has to be "D3, D6, D9, D12 etc."

It seems COUNTIF does not handle non-contiguous ranges even if defined as a Range Name.

Problem is now solved by setting up tables with the individual entries assigned and using those for COUNTIF.

One of the beauties of Excel is the no of ways problems can be resolved!

Thanks again.

Peter Moran

 

[zelgar]

well, the following should work:
=COUNTIF(D3,5)+COUNTIF(D6,5)+COUNTIF(D9,5)+COUNTIF(D12,5)

 

[SkipVought]

@zelgar

Has to be "D3, D6, D9, D12 etc."

I hate to do that "etc" times ;-)

Without seeing the actual worksheet structure & understanding its design intent or having a clear understanding of it, it's difficult to posit a solution.

The sad truth is that many neophyte users design workbooks without understanding the power of well structured tables and the functions and features that are at their beck and call, and consequently shoot themselves in the foot, by severely limiting the capabilities of Excel.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

This modification of Skip's suggestion with MOD works to count the cells in (D3, D6, D9, D12) that are equal to 5. It is also easily expanded.
=SUMPRODUCT((MOD(ROW(D312),3)=0)*(D312=5))