How can generate a weekly report using formula and without using a Column Helper?

Benjo09

Hi,

Please help me on writing a formula to calculate the weekly sales data. Please see the attached screenshot of my example data.

Paul Newcome

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")

Benjo09

Hi Paul,

Thank you for the formulas.

Should I only write @cell or I should select a cell?

Paul Newcome

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.

Stefan

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

Paul Newcome

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")

Benjo09

Hi Paul,

Thank you for the formulas.

Should I only write @cell or I should select a cell?

Stefan

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:

Create Efficient Formulas with @cell and @row | Smartsheet Learning Center

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Optimize your formulas to improve the overall performance of your sheet, and prevent having to manually reference cells in formulas. 

Hope this helps too

Stefan

Paul Newcome

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.

Benjo09

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?

Stefan

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

Benjo09

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.