Monday, June 10, 2013

yogi_Compute Row By Row Number Of Orders Placed On Specified (Successive) Dates

                                          Google Spreadsheet   Post  #1236
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jun 10, 2013
user Sean Snyder (!category-topic/docs/spreadsheets/SwvsQRsqJsM)
Increment the date within a formula
I'm currently creating a spreadsheet that has the summary of the parts we ordered. For sake of simplicity, here's my formula:

=COUNTIF('April 13'!$F$1:$F$200,41389)

The 41389 is the numerical value of the reference date. I want to copy/paste this formula for the rest of the month but not have to increment the date by one digit for each day. Is there a quicker way of doing this? Thanks!
So I have a sheet labeled as "April 13" and it has an example data below:

Date of Order | Number of Parts Ordered | Date of Completion |
 4/2/2013                      5                                 4/5/2013
 4/2/2013                      3                                 4/3/2013
 4/3/2013                      1                                 5/1/2013

This April sheet just has each line. By using the COUNTIF function, I can find out how many orders occurred on that date in this sheet. However, an example of my summary page is as follows:

Date      | Number of Orders | Number of Parts | Number of Completed Orders |
4/2/2013             2                           8                                0
4/3/2013             1                           1                                1

That means I have to put this formula in each row and increment only the date by 1 value. I use a different function for the "Number of Parts" column. I'm just looking for an easy way to increment a value in a formula that isn't a cell value. Hope this clears things up.