Need a formula for counting information based on date

Hello,

I need a formula that can help me calculate the sum within a column based on date. Not sure if this can be achieved. We are updating information on a weekly basis and I have different rows for the same date. I need a formula to give me the sum based on the date. For example, on 1/17 I will have 6 rows with data that shows hours of worked for each team. This week on Friday we will have new data, so I want the formula to be able to tell me what's my sum for the whole week. If this makes sense, like below, on the Sum column I want the total hours for week ending 1/17 and 1/24 and so forth. I am open to using anything, if anyone has any other suggestions on representing this, please let me know.

Thanks,

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

Here is another rather flexible option...

=SUMIFS([Total Hours]:[Total Hours], [Week Ending]:[Week Ending], [Week Ending]@row)

This will sum all of the numbers in the [Total Hours] column that have the same date in the [Week Ending] column as the date in whatever row the formula is on.

All comments

L_123

=SUMIF([week ending]:[week ending], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Total Hours]:[Total Hours])

The formula above should show the sum of week. You can change the "today()" part of the formula to whatever you like to get different results, as I don't think I fully understand how you are trying to format your sheet.

Jona Gjylameti

Hi L@123,

thanks for sending over the formula, however that changes as the days move along, like Today's date would change tomorrow and I wouldn't be able to tell what was the sum for week of 1/17.

Basically I want to know how many total hours I had on week ending 1/17 and the week after and so forth. I still want that visibility for previous weeks.

@Diana

@Jona Gjylameti try this formula, it is very specific to the day.

=SUMIFS({Total hours},{Week ending}, >=DATE(20, 1, 17), {Week ending}, <=DATE(20, 1, 24))

hope this helps.

Paul Newcome

Here is another rather flexible option...

=SUMIFS([Total Hours]:[Total Hours], [Week Ending]:[Week Ending], [Week Ending]@row)

This will sum all of the numbers in the [Total Hours] column that have the same date in the [Week Ending] column as the date in whatever row the formula is on.

Jona Gjylameti

@Diana thank you!

@Paul Necome this is great, thanks a lot!

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others with a similar challenge know that a solution is available.

Sign in to join the conversation!

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.