1

Is there a more efficient way of making the following function?:

>=COUNTIF(A1,">0")+COUNTIF(C1,">0")+COUNTIF(E1,">0")+COUNTIF(G1,">0")

I want to count if the cell is bigger than 0 and sum it. This function gets long and complicated if there are more than 3 cells with different ranges that I want to count.

Improve this question
2
  • Are you trying to determine if the cell is not blank or if the cell contains a number that is greater than 0? Your current formula will count the number of cells that have values greater than zero.
    – wbeard52
    Oct 2, 2015 at 3:47
  • There are possibly negatives in these cells? Because if you can change your formula to display zeroes when the result is negative, then a regular sum function will only add the positive values anyway. For instance if A1=3-7 you could wrap it with =MAX(0,A1) which will convert it to zero. But you may still want to see the negatives so not sure if that will work for you
    – datatoo
    Oct 2, 2015 at 23:17

2 Answers 2

Reset to default
1

If I understood correctly you want to count every other cell (cell with an odd column index) with value greater than zero:

=SUMPRODUCT((MOD(COLUMN(A1:Q1),2)=1)*(A1:Q1<>"")*(A1:Q1>0))

This will do it for range A1:Q1, counting cells A1, C1, E1, G1, I1, K1, M1, O1, Q1 with value >0.

Improve this answer
0

I think you want to sum the cells that have a number greater than zero. If that is the case use this formula.

=SUMIFS(A1:C1,A1:C1,">0")

If you want to count the number of cells that with values greater than zero use this formula

=COUNTIFS(A1:C1,">0")
Improve this answer
1
  • I want to count the numbers of cells with values greater than zero, so the function =COUNTIFS(A1:C1,">0") is good, but the tricky part is the range. I want to count alternate columns cells. That would be A1, C1, E1, skipping one column each time.
    – Voltic
    Oct 7, 2015 at 2:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .