form data capture

Habitat John

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

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

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.