How do I extract data from a column that represents a week or month?

Wolfram · 2021-11-02T10:50:51+00:00

There was an error rendering this rich post.

Lets say I count apples sold and I input the data in column [Number of apples sold] which is next to a date column. Now I want to find out how many apples I sold in a given week. I use this formula:

=SUMIFS([[Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = 44, Date:Date, YEAR(@cell) = 2021)

This works but I have to change the WEEKNUMBER for every week which is a lot of work and I will also have to update the YEAR figure every year.

I use the same method for extracting "apples sold" on a monthly basis with the same problem.

I think the solution is to compare the WEEKNUMBER of the Date column with a helper column that has the WEEKNUMBER repopulated. I just can't get that to work.

Thanks for the help!

Wolfram

Find more posts tagged with

Formulas

Accepted answers

Wolfram

@Genevieve P.

Thats perfect. Thanks.

Genevieve P.

Hi @Wolfram

You'll need to have the week or month listed somewhere, either directly in the formula or in a cell you can reference.

For example, if you have a column with all the week numbers listed down each row, then you can use the cell from that column as the week number to look for:

=SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = [Week Number]@row, Date:Date, YEAR(@cell) = Year@row)

Is that what you were looking to do?

Cheers,

Genevieve

All comments

Wolfram

@Genevieve P.

Thats perfect. Thanks.

Genevieve P.

Hi @Wolfram

You'll need to have the week or month listed somewhere, either directly in the formula or in a cell you can reference.

For example, if you have a column with all the week numbers listed down each row, then you can use the cell from that column as the week number to look for:

=SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = [Week Number]@row, Date:Date, YEAR(@cell) = Year@row)

Is that what you were looking to do?

Cheers,

Genevieve

Wolfram

Hi @Genevieve P.

Thanks!

I don't want to have the data for last week or last month. I want to have the data for a given week or month. E.g. week 22 or month 8.

What I want to avoid is having to input the week number 52 times.

Genevieve P.

Hi @Wolfram

You can use the TODAY function to help you with this!

Are you looking to calculate last week's totals? If so, we can find the WEEKNUMBER(TODAY()) - 1, which will tell us last week's number.

Then we can do the same thing with the YEAR function... YEAR(@cell) = YEAR(TODAY())

Try this:

=SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY()))

For your month formula, I would presume it's the same sort of thing. If you want to find last month, you could do this:

=SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY()))

However this gets a bit complicated if you're in January. I would write two formulas and have an IF statement identify what month you're in, like so:

=IF(MONTH(TODAY()) = 1, SUMIFS([[Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = 12, Date:Date, YEAR(@cell) = YEAR(TODAY()) - 1), SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY())))

Let me know if this is the right solution for you or not.

Cheers,

Genevieve

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.