SUMPRODUCT syntax/problems

[Auser]

Hiya,

I have a preset range on my worksheet (A1401) where approximately a
quarter of the sheet is currently 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 blankl/empty cells).

Your further advice and comments would be appreciated as I've been at this
for three days!

Thanks

 

[Dave R.]

Shouldn't it be picking up blank/empty cells? You say you want to count when
there isn't a date in the end-date column. Wouldn't a blank, or empty cell
count as NOT a date?

Seems you should want to count blanks in B2:b401, in which case

=SUMPRODUCT((Main!$A$2:$A$401>100)*(Main!$B$2:$B$401=""))

should work, and be just a bit easier to understand what it's doing.