again count for range greater than another

**besbesmany** · 08-20-2012, 12:33 PM

I've posted before the following question and get answer
http://www.excelforum.com/excel-new-...html?p=2899206

The problem now if some cell is empty and contains no number
I want to exclude the whole row from counting

using =SUMPRODUCT(--(B1:B5>A1:A5))

if cell A5 is empty the answer is counting 1 and formula understanding that B5>A5 which is not true

I want not to count the this case in formula

**NBVC** · 08-20-2012, 01:19 PM

use:

=SUMPRODUCT(--(B1:B5>A1:A5),--(A1:A5<>""))

**besbesmany** · 08-20-2012, 01:33 PM

can I use this for both cases

=SUMPRODUCT(--(B1:B5>A1:A5),--(A1:A5<>""),--(B1:B5<>""))

Thanks so so much

**NBVC** · 08-20-2012, 01:41 PM

sure, but I think you only need what I gave, as if B1:B5 is blank it will not be greater than A5, even if A5 is also blank.

**besbesmany** · 08-20-2012, 01:49 PM

yes , thanks so much for your help

**arlu1201** · 09-03-2012, 04:27 AM

Besbesmany,

It looks like your problem has been solved. This time, i will mark it [SOLVED] for you. Next time please ensure you do it for your threads.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.