Sum of quantities on a given date

steve50951

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

 

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

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.

steve50951

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

steve50951

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

steve50951

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

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}

steve50951

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

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

.

??

steve50951

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

so what you have listed above is correct

steve50951

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

Paul Newcome

That would be exactly it!