countif

[iainsteel]

Hi
I trying to add up positive cells in excel with countif except that the cells in question are not in a range.
So normally
=COUNTIF(L20:L30,">0")
But the cells i wish to count are 3 cells apart so I thought
=COUNTIF(L20,P20,T20,X20">0")
would work , but it doesnt.
Can anyone help?
Thank you

 

[jasonb75]

one way

=SUM(L20>0,P20>0,T20>0,X20>0)

 

[JackDanIce]

Try:

=SUM(COUNTIF(INDIRECT({"L20","P20","T20", "X20"}),">0"))

 

[jasonb75]

Try:

=SUM(COUNTIF(INDIRECT({"L20","P20","T20", "X20"}),">0"))

I forgot you could do that and only used a similar formula yesterday although personally I would go with

=SUM(COUNTIF(INDIRECT({"RC12","RC16","RC20","RC24"},0),">0"))

instead, that is assuming the formula is going in row 20.

 

[JackDanIce]

I forgot you could do that and only used a similar formula yesterday although personally I would go with

=SUM(COUNTIF(INDIRECT({"RC12","RC16","RC20","RC24"},0),">0"))

instead, that is assuming the formula is going in row 20.

True but as user, I prefer to use A1 than R1C1

At the end of the day it's just about personal preferences!

 

[barry houdini]

you can use this version, especially for larger ranges

=SUMPRODUCT((MOD(COLUMN(L20:X20)-COLUMN(L20),4)=0)*(L20:X20>0))

 

[jasonb75]

True but as user, I prefer to use A1 than R1C1

At the end of the day it's just about personal preferences!

And practicality, I avoid R1C1 whenever possible, but it's the only way to make an INDIRECT reference relative.

 

[iainsteel]

Thanks Chaps
I appreciate the effort just gonna figure out which one
Cheers