Countif's or Array

  • Thread starter Thread starter Auser
  • Start date Start date
A

Auser

Afternoon,

Within the spreadsheet that I'm working on I have a 'Lapsed Days' column and
and 'End Date' column. 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 have tried various versions of nested countif's and have dabbled with an
array formula:
{=SUM((Main!$A$2:$A$401>100)*(Main!$B$2:$B$401>DATEVALUE(01/01/2004))}

with little success.

Any help/pointers would be greatly appreciated.

Thanks
 
One way

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

assuming that no dates are blank,
if text and blanks can be counted as no dates

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