Need a formula for counting something on a weekly basis.

Aparanji Veldurthy

From the screenshot below, I need a formula to count the incoming mail based on each week. I want to show the information on a dashboard on a weekly basis which will exclude the weekends. For example here, I have the data for last week of Jan, but when we enter into next week, first week of feb, I want the dashboard to show new numbers as we enter daily. Is this possible?

Genevieve P.

Hi @Aparanji Veldurthy

You can use the WEEKNUMBER function to find the rows that have Today's Weeknumber in the Date column. Then you can embed this criteria in a SUMIF statement.

A SUMIF works like this:

=SUMIF([Column 1]:[Column 1], "Criteria 1", [Sum Column]:[Sum Column])

In your case, the criteria is as follows:

WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())

So, put together, try this:

=SUMIF(Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Incoming Mail]:[Incoming Mail])

You can have this input into a Sheet Summary Field (see here) and then use this in a metric widget.

Let me know if this works for you or if you have any additional questions!

Cheers,

Genevieve

Genevieve P.

Hi @Aparanji Veldurthy

You can use the WEEKNUMBER function to find the rows that have Today's Weeknumber in the Date column. Then you can embed this criteria in a SUMIF statement.

A SUMIF works like this:

=SUMIF([Column 1]:[Column 1], "Criteria 1", [Sum Column]:[Sum Column])

In your case, the criteria is as follows:

WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())

So, put together, try this:

=SUMIF(Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Incoming Mail]:[Incoming Mail])

You can have this input into a Sheet Summary Field (see here) and then use this in a metric widget.

Let me know if this works for you or if you have any additional questions!

Cheers,

Genevieve