0

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.

Matt
  • 165
  • 2
  • 3
  • 13
  • Things are a little confusing. On your new Sheet AVAIL you state B1 is a date. later on you state B1 will be your countif formula. can you please clarify this. if this is not the case what sheet are you using the countif formula on? – Forward Ed Apr 15 '16 at 13:46

1 Answers1

0

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))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    Damn you're awake already scott! I was going to suggest the same, but got confused reading his description of the data lay out. – Forward Ed Apr 15 '16 at 13:51
  • Scott, thanks a mill. I've been trying to work it out for hours, and you nailed it for me. Works perfectly, and it's now so obvious. Thanks again. – Matt Apr 15 '16 at 14:37