Sumproduct with cells >0

  • Thread starter Thread starter fr8dog
  • Start date Start date
F

fr8dog

I'm having issues counting cells with a value of >0. The cells that I'm
counting are being filled by another function, but will always be a number.
When counting the cells that are <0 it works fine, but when I try it with >0
it is counting the cells no matter what is in them (negative number, blank,
text). What is wrong?

Here is the one for the <0 that is working fine:

=SUMPRODUCT(--(MOD(ROW(R6:R243),3)=0),--(R6:R243<0))

And this is the one I can't seem to get to work:

=SUMPRODUCT(--(MOD(ROW(R6:R243),3)=0),--(R6:R243>0))
 
Yup, thought of that right after I posted, in the function that was filling
it I had a " " in one of the if's. I changed that and it works fine now, but
now I have another question of course.

I have a third sumproduct function that counts the cells that =0. So
basically I'm keeping a record of the positive numbers, negative, and the
0's. When counting the =0 cells, it is counting the cells that have not been
filled yet. I'm putting in data on a day-to-day basis, so those cells will
fill in as I go, but I don't want it to count the cells that are blank at the
moment?? Any info on that one?

=SUMPRODUCT(--(MOD(ROW(R6:R243),3)=0),--(R6:R243=0))

I want to make that one count only the cells that have a number in it and
not the blanks.
 
Just add another test for that condition...

=SUMPRODUCT(--(MOD(ROW(R6:R243),3)=0),--(R6:R243=0),--(R6:R243<>""))

Rick
 
Back
Top