form data capture

I am collecting daily sales totals and transaction totals with a form. The store manage enters the totals into a SS form daily and it populates the SS. I would like the entries to sub total into months as they are added to the SS... then those monthly totals can roll up onto a dashboard, along with other department information.

Help please

John

Find more posts tagged with

Forms

Sales

Non-profit

Sheets

Comments

Andrée Starå

Hi John,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Habitat John

Pretty simple really.....

Daily Manager adds

date 1/1/19

daily sales $2500

Transactions 100

to a form i make... and that populates the SS, adding that line to the bottom or top of the SS, whatever I choose.

The problem is it will just keep adding rows without a break for a January monthly subtotal before it goes onto to posting February's daily sales...... I could just let it roll into the next month and go in there and manually insert a line to add a subtotal for the previous month, but I am just trying to automate the process

Andrée Starå

Ok.

Try this.

Below is the formula for January (change the 1 to 2 for February and so on)

In my example, the formula is placed in the Totals column.

=SUMIF(Date:Date; MONTH(@cell) = 1; [Daily sales]:[Daily sales])

The same version but with the below changes for your and others convenience.

=SUMIF(Date:Date, MONTH(@cell) = 1, [Daily sales]:[Daily sales])

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Image 2019-01-27 at 12.47.25 fm.png

Paul Newcome

When using forms to populate a sheet, I tend to use a second sheet for my metrics just to make sure things don't get mixed up. It's just a personal preference of mine. For something like this (almost identical in fact), I use a separate sheet laid out like this...

- 2019

January 1

February 2

March 3

I have the year as the parent row. The children rows of the Primary Column are the months. I then have a helper column with each month number off to the right somewhere (it eventually gets hidden).

I then use something along the lines of

=SUMIFS({Master Sheet Range 1}, {Master Sheet Range 2}, AND(MONTH(@cell) = [Helper Column]@row, YEAR(@cell) = PARENT([Primary Column]@row)))

Master Sheet Range 1 is the cross sheet reference for the column you want to add in your Master Sheet.

Master Sheet Range 2 is the cross sheet reference for the date column in your Master Sheet.

[Helper Column]@row is referencing the helper column on the summary sheet that I used to designate the month numbers.

PARENT([Primary Column]@row is referencing where I have my year entered on the summary sheet.

This will basically sum up everything in the range you want to add if the corresponding date's month matches the helper column and the year matches the parent row of the summary sheet.

This will also allow you to use an =SUM(CHILDREN()) in the parent row next to the year in the same column as your monthly summaries to have a YTD summary. You can then just copy/paste the entire section and change the year to continue tracking multiple years from the same master sheet.

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…