Totalling by referring to 2 date ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting Ranges 2
Excel 2 into 1 6
VBA set thick borders around non empty cells in range 0
Date between date range 1
SUMPRODUCT 7
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7
Sumif formula 4
Changing range based on Date 5

Back
Top