SUMPRODUCT & other formulas

[Auser]

Thanks for all the help I've received up to now, it's been invaluable.

My original question was:
"I have a preset range on my worksheet (A1401) where approximately a
quarter of the sheet is used. Within this spreadsheet I have a 'Lapsed Days'
column and and 'End Date' column (both of which can be empty). I am looking
to create a single cell calculation that tells me the number of entries
whose 'Lapsed Days' are greater than 100 where there isn't a date (any date)
in the 'End Date' column. I've tried:

=SUMPRODUCT(--(Main!$A$2:$A$401>100),--(NOT(ISNUMBER(Main!$B$2:$B$401))))

as kindly suggested by Peo Sjoblom but to no avail (it seems to be picking
up blank/empty cells)."


I've now discovered that the formula in $A$2:$A$401 (one row example):

=IF(ISBLANK(I2),
"",IF(ISBLANK(O2),DAYS360(I2,TODAY(),TRUE),DAYS360(I2,O2,TRUE)))

which gives me the number of elapsed days between either Start/End date (if
there is an end date) or Start/Current date, is causing the SUMPRODUCT
formula above to produce incorrect results. This is because the SUMPRODUCT
appears to be picking the 'Lapsed Days' formula up and including it in its
result. I've double checked this by inserting this test formula temporarily:

=IF([Lapsed Days cell]>100,1,"")

with the result being 1 (confirming my suspicions).

I would now like to know how to achieve the answer to my original question,
whilst counting the occurences of >100 in the 'Lapsed Days' column (where a
figure actually appears), but discounting the physical formula in the cell
otherwise (if the cell is blank).

Sorry if this is a bit long-winded, but I think that the explanation
requires it.

Thanks

 

[Andy B]

Hi

Have you tried something like:
=SUMPRODUCT((Main!$A$2:$A$401>100)*(Main!$B$2:$B$401<>""))