Shorten This Formula

[excelforum123]

Hello,

I have many number values in different non-contiguous cells, I have to count the cells having value >1 only.

As the cells are not adjacent, the regular formula does not work.

I tried the following and got the desired result :

=(COUNTIF(A4,">1")+COUNTIF(C4,">1")+COUNTIF(E4,">1")+COUNTIF(H4,">1")+COUNTIF(I4,">1")+COUNTIF(K4,">1")+COUNTIF(M4,">1")+COUNTIF(N4,">1")+COUNTIF(O4,">1"))

But By this way, Every time I will need to enter this big formula.

Is there any way to Shorten this formula? or Can you suggest any other solution?

Basically My Query is :

A1 : 3
B1 : 0
C1 : 1
d1 : 2
e1 : 0
f1 : 5
I have to count the cells having value ">1", i.g. (in current case) countif(A1, D1, f1,">1")

Please Suggest.

[DonkeyOte]

You could use:

Please Login or Register  to view this content.

ie use contiguous ranges where you can

If on the other hand there are "flags" denoting the coumns to use in another row - eg A1,C1 etc contain "x" then

Please Login or Register  to view this content.

[excelforum123]

Thanks, It worked !!.

Although I don't have any continues cells, I done some changes and I figured it out.

Thanks again.

[DonkeyOte]

OK - FWIW another (Volatile) alternative would be:

Please Login or Register  to view this content.