CODEWITHSUNDEEP
excelfunctiondatepastecountif
Matt
Matt

Reputation: 165

Countif DATE - Paste formula & increase date by 1 day, using same range to reference from

A beginners question I expect. I'm tinkering with a new Google sheet called AVAIL for work. It will show for each day how many guests are booked onto the dive boats we use, to help us plan future bookings.

I'm referencing the same range from another sheet named MER, which has guest bookings listed.

The new sheet AVAIL has all calendar dates, with each date in a new column. For example: 22/04/2016 in B1, 23/04/2016 in C1 etc, right through for the next six months

The formula I'm using works fine =COUNTIF(MER!$A4:$A2000,"22/04/2016")

But, I'd like to copy the formula to adjacent columns, increasing the date by one day each time, referencing the same range though. For example: B1 would be =COUNTIF(MER!$A4:$A1000,"22/04/2016" C1 would be =COUNTIF(MER!$A4:$A1000,"23/04/2016" D1 would be =COUNTIF(MER!$A4:$A1000,"24/04/2016" and so on.

I have several dive boats to do this for, and I know there must be a way to copy the formula increasing the date by one day. To change each one manually would take me a long time.

Any help greatly appreciated, thank you.

Upvotes: 0

Views: 1174

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

Put the date reference in the first row. Then add a second row. since you have the date reference at the top of the column just put the cell reference in for the date:

=COUNTIF(MER!$A4:$A2000,B1)

If you do not want to do that then try this this:

=COUNTIF(MER!$A4:$A2000,DATE(2016,4,22 + COLUMN(A:A)-1))

Upvotes: 0

Related Questions