Totalling by referring to 2 date ranges

M

Mike

I have a spreadsheet that has 2 columns of date ranges.
I want to refer to the first column and count how many entries meet a
specific date range criteria. If there is no entry in the first column, I
want to refer to column 2 to see if the range matches.
Can somebody help????
Thanks
 
J

Jason Morin

Try:

=SUMPRODUCT((A1:A100>=D1)*(A1:A100<=E1)+(A1:A100="")*
(B1:B100>=D1)*(B1:B100<=E1))

where D1 = start date, E1 = end date.

HTH
Jason
Atlanta, GA
 
M

Mike

perfect! thanks for the help!

Jason Morin said:
Try:

=SUMPRODUCT((A1:A100>=D1)*(A1:A100<=E1)+(A1:A100="")*
(B1:B100>=D1)*(B1:B100<=E1))

where D1 = start date, E1 = end date.

HTH
Jason
Atlanta, GA
Click to expand...
 
Top