Countif

[anniemalec]

Hello

I'm trying to write a formula to count cells c3,e3,g3 etc. but only if they contain a specific formula - i.e. "=count(b3)"

There's about 40cells I will need to count from.

I've tried =countif((c3.e3.g3 etc),="%count%","0") but is coming back incorrect.

Hope that makes sense...

Thanks in advance.

 

[Gerald Higgins]

Hi. Excel is not good at working out whether a cell contains a formula or not, and then using the result to drive another formula.

 

[Dannyh1]

If you have MOREFUNC downloaded, you could use FORMULATEXT in column D & then do your count in against column D

 

[anniemalec]

No don't have that downloaded and can't install anything to this workstation due to the restrictions on my work network.

Can anyone think of anyway to get around it?

 

[anniemalec]

Now trying to keep it simpler and only count if the cells have the value "1"

still struggling to get the syntax right however...

countif((CN3:CV3,CC3:CK3,BR3:BZ3,BA3:BO3,AJ3:AX3,S3:AG3,B3:P3)="1","0")

I now have too few functions.

Cheers.

 

[Gerald Higgins]

The basic syntax for COUNTIF is like this

=countif(cn3:cv3,1)

I'm not sure you can do it for multiple ranges, I'm certainly struggling with that.

But you could use

=countif(cn3:cv3,1)+countif(cc3:ck3,1)+....

Not elegant, but should work.

 

[anniemalec]

Thats worked perfectly for that, thanks!

I'm now having difficulty with counting if between 2 values.

=COUNTIF($CN3:$CV3,">=2.5,<=4") but its coming back nil.

can't use a similar method as with previous formula as if i do each value is counted twice.

 

[Gerald Higgins]

Try this

=sumproduct(--($cn3:$cv3< =4),--($cn3:$cv3> =2.5))