How can generate a weekly report using formula and without using a Column Helper?
Hi,
Please help me on writing a formula to calculate the weekly sales data. Please see the attached screenshot of my example data.
Best Answers
-
Give these a try...
Total QTY:
=SUMIFS(Qty:Qty, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))
Total Qyt of Apples:
=SUMIFS(Qty:Qty, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), Item:Item, "apple")
Total Sales of Apples:
=SUMIFS(Price:Price, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), Item:Item, "apple")
-
Hi Paul,
Thank you for the formulas.
Should I only write @cell or I should select a cell?
-
Just use @cell. Basically it tells the formula to evaluate the previously specified range on a cell by cell basis instead of the entire range as a single unit.
-
Hi,
I have these issues also from time to time and often when I copy & paste a formula.
Most of the times the formula has grey colours only then and it looks like Smartsheet has not yet made a connection to the sheet the formula is in. Usually removing the last bracket, adding a space and then the bracket again helps.
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Answers
-
Give these a try...
Total QTY:
=SUMIFS(Qty:Qty, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))
Total Qyt of Apples:
=SUMIFS(Qty:Qty, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), Item:Item, "apple")
Total Sales of Apples:
=SUMIFS(Price:Price, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), Item:Item, "apple")
-
Hi Paul,
Thank you for the formulas.
Should I only write @cell or I should select a cell?
-
Hi,
as I understand it, the price column shows the price per unit.
So
=SUMIFS(Qty:Qty, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), Item:Item, "apple")
needs to be multiplied with the cost per unit, right?
=SUMIFS(Qty:Qty, Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), Item:Item, "apple") *2$
BUT... are the prices per unit fixed, or can they vary from week to week? You may consider using a cell for unit prices.
@Benjo09 The usage of @cell in the formulas is a must in this case. Have look here to learn more:
Hope this helps too
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Just use @cell. Basically it tells the formula to evaluate the previously specified range on a cell by cell basis instead of the entire range as a single unit.
-
Hi Paul & Stefan,
Thank You very much for helping me out. The data table is just a dummy table. I am applying the formula now in the actual sheet and referencing into another sheet.
There are occasions like when I am writing the first formula it is okay and then the next ones is resulting into #UNPARSEABLE.
Do you have tips to avoid this specially when you are writing a formula and referencing across to another sheet?
-
Hi,
I have these issues also from time to time and often when I copy & paste a formula.
Most of the times the formula has grey colours only then and it looks like Smartsheet has not yet made a connection to the sheet the formula is in. Usually removing the last bracket, adding a space and then the bracket again helps.
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Thank you for the tips Stefan. It took me a great time when I am doing my summary table and when I am trying to paste some formula it prompts an error.It looks like It need to be referenced it over and over again. I hope that reference from another sheet could have a fixed symbol like $ in excel so that It will not changed or get overwrite if I wrongly reference in the series of my formulas in my summary sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!