Sum of quantities on a given date

steve50951
steve50951 ✭✭
edited 12/09/19 in Formulas and Functions

I have a date column for installs. I also have a column where we enter a quantity.

I am trying measure installs on each day of the month.

We want to be able to look at a particular date and see how many installs we have on this date.

We have about 120 properties

Im thinking a roll up sheet with each of the properties on a row.. Maybe a column for each day of the month? Then maybe a formula that would calculate the quantity on a each day?

Im not really sure if this is the best way or not. Looking for some suggestions for the best way go about measuring installs on each day of the month.

Ive Included a screen shot of the install and quantity columns.

Also included a possible sheet solution? Again, not sure if its the best way to setup or not.

Thanks for your help.

SGF

 

Screen Shot 2019-08-27 at 10.09.42 AM.png

Screen Shot 2019-08-27 at 10.33.02 AM.png

Tags:

Comments

  • Sheryl P
    Sheryl P ✭✭✭✭✭

    I was able to use countifs "install date" - I set a few dates in the "header" - today, today -1 (for installs yesterday) and I set the start/end of week - so I could get counts for the week.  The countifs then referenced those dates to decide whether or not to count.  I had other criteria in the countifs as well.

    Haven't used the new summary report feature yet - can't speak to that.

     

    Hope that helps.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest a date column and then have your properties listed out along a single row. This will allow you to use a very basic SUMIFS and reference cells instead of having to hardcode dates into your formulas. Column headers in the example below are in bold.

    .

    Date          Property A          Property B          Property C

                      Property A            Property B           Property C

    1/1/19

    1/2/19

    1/3/19

    1/4/19

    .

    In the first cell of the table ([Property A]2), you would enter something along the lines of this...

     

    =SUMIFS([Install QTY]:[Install QTY], [Install Date]:[Install Date], $Date@row, [Install Property]:[Install Property], [Property A]$1)

    .

    NOTE: The $ to lock in the Date column reference and the 1 row reference respectively.

    .

    Once you enter this into [Property A]2, you can then dragfill down and over to have it reference the appropriate dates and property names within the formula to avoid having to manually update for each variable.

  • I think i understand, but im missing something.

    Here's the formula i used...

    =SUMIFS([7900 Park Central Install QTY]:[7900 Park Central Install QTY], [7900 Park Central Install Date]:[7900 Park Central Install Date], $Date@row, [7900 Park Central Property Info]:[7900 Park Central Property Info], [7900 Park Central]$2)

    It gives me an #UNPARSEABLE

    does it have to do with using "[" rather than "{"

    I attached a couple screen shots

    Screen Shot 2019-08-29 at 7.56.32 AM.png

    Screen Shot 2019-08-29 at 7.56.47 AM.png

  • So in working through it this is the formula i ended up with

    =SUMIFS({7900 Park Central Install QTY:7900 Park Central Install QTY}, {7900 Park Central Install Date:7900 Park Central Install Date}, $Date@row, {7900 Park Central Property Name:7900 Park Central Property Name}, [7900 Park Central]$1)

    It gives me a invalid reference response.

    screenshots included

    Screen Shot 2019-08-29 at 8.57.43 AM.png

    Screen Shot 2019-08-29 at 8.57.50 AM.png

  • I think im close, but formula still returning "Invalid ref"

    =SUMIFS({7900 Park Central Install QTY:7900 Park Central Install QTY}, {7900 Park Central Install Date:7900 Park Central Install Date}, $Date@row, {7900 Park Central Property Name:7900 Park Central Property Name}, [7900 Park Central]$1)

    feels like im just missing a bracket or () somewhere.

    any ideas?

    thanks,

    Steve

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you referencing data on the same sheet as the table or on a different sheet? That is going to make the difference between using 

     

    [Column Name]:[Column Name]

     

    and 

     

    {Sheet Name Range Name}

  • referencing both same sheet and different sheet for the ranges.

    so i corrected the brackets in the formula but now it returns #UNPARSEABLE

    =SUMIFS({7900 Park Central Install QTY}:{7900 Park Central Install QTY}, {7900 Park Central Install Date}:{7900 Park Central Install Date}, $Date@row, {7900 Park Central Property Name}:{7900 Park Central Property Name}, $[7900 Park Central]$1)

    any ideas why its still unparseable?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are actually a few reasons why.

    Curly brackets denote a range from another sheet. This range is established by going through the appropriate route of selecting the "Reference Another Sheet" link in the formula help dialog box. When using a cross sheet reference, you will select the range and leave it as is. You do not repeat the range with a : in between.

     

    Square brackets denote a column name on the same sheet as the formula. These would be separated by a : if there is a start and stop cell for the range or if it is just the column name(s) to reference entire column(s).

     

    So let's break this down piece by piece to figure out which bracket types are required where and how to properly use them...

     

    Here is a listing of what appears to be each of your column names referenced in your formula. Which one's are on the other sheet, and which ones are on the table sheet?

    .

    7900 Park Central Install QTY

    7900 Park Central Install Date

    Date

    7900 Park Central Property Name

    7900 Park Central

    .

    I am assuming it will be 

    .

    Other

    Other

    Table

    Other

    Table

    .

    ??

  • date & 7900 Park Central are the table sheet. the others are the "other" sheet

    so what you have listed above is correct

  • BTW...The way you broke it down...THANKS SO MUCH! I didnt realize the difference in the square brackets and the curly brackets. Makes sense now.

    so based on that, should it look like this...

    =SUMIFS({7900 Park Central Install QTY}, {7900 Park Central Install Date}, $Date@row, {7900 Park Central Property Name}, $[7900 Park Central]$1)

    This seems to work. Want to be sure though

    thanks again.

    It was a HUGE help!

    Steve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!