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

Monthly feedback from multiple individuals
Hello community, I need help figuring out how to set up a sheet to collect monthly comments on several projects. Each project is assigned to a different person who need to provide monthly feedback on its progress. Ideally, I would like them to receive a reminder notification on the report due date, followed by reminders if…
Sheet shows BLANK rows, there is a "MOVE rows" worflow, is that why?
Is it possible that a workflow that MOVES rows to another sheet too basically do a CUT/PASTE (like in Excel), and then you are left with BLANK rows on your original sheet? I cannot think of any other LOGICAL reason why this would happen. The users enter the data from an Intake Form, it gets placed onto this sheet. They do…
Sorting not working?
Hi. I have a sheet where I am sorting dates by ascending order. But the sorting functionality doesn't seem to be working. Instead of bringing all items with date values to the top of the sheeting and sorting them in ascending order, it leaves a gap. Is it a result of the "Next Review Due" column containing a column formula…