Using Countifs with dates, how to fill down the formula but with the next upcoming date

Hi,

I was successfully able to use the countifs formula to cross reference another sheet to count the amount of times on a specific date a certain response came up.

=COUNTIFS({Case Log Range 1}, =DATE(2021, 8, 1), {Case Log Range 2}, "Positive")

What I would like to do next if figure out how to fill down the date in the following rows using a formula versus updating each date in each cell manually. I already have the date in the row of the response count.

Following that, I would like to be able to use either a countifs or sumifs to do a count or sum weekly example count of the number of positive responses from 08/02/2021 to 08/08/2021 the from 08/09/2021 to 08/15/2021 and so on. I also have the date of the week that I am going to reference in the source sheet.

I currently have the weekly count in a separate spreadsheet but if there is a better or easier way to do it all in ne sheet happy to hear that suggestions as well. Thanks.

Find more posts tagged with

Accepted answers

Lidiya S.

@JD J

Awesome that you were able to solve the first part of the problem!

No problem about rephrasing. On your daily sheet, you would add a column for "Week Number" and put the following formula in

=weeknumber(DATE@row)

This will look up the date and return which week of the year that date falls under.

In your summary sheet you would also add a column for calculating the week number of the "Week Of" date. Pretty much this is just a way to make sure you are capturing all the dates in the daily sheet that occur in a specific week instead of having to manually select the dates.

The final step could be to sum the count using the following formula

=sumif(reference the week number column of your daily sheet,
=weekofnumber@row,reference the positive column in your daily sheet)

=sumif(criteria column, criteria, sum column)

Below is a screen shot of the way it would work.

Hope this helps! Please @ me if more clarification is needed or anything, always happy to help :)

Lidiya Shutaya

All comments

Lidiya S.

JD,

Hope you are well!

If you want to turn that formula from a row formula into a column formula you can adjust your formula to be:

=COUNTIFS({Case Log Range 1}, [Date]@row, {Case Log Range 2}, "Positive")

For summing the # of positives for the week, you could add another column to the daily sheet with the formula =WEEKNUMBER([Date]@row). This would return the week number. For example, =WEEKNUMBER(8/2/21) would return a value of 31.

And then in your weekly sheet to the right of your [Week Of] column your formula could be:

=SUMIF({Range would be in your daily sheet the column that has the weeknumber}, =WEEKNUMBER([Week Of]@row), [Positive Count]:[Positive Count])

Let me know if this helps! There are few more ways to do this but I always recommend using helper columns because it keeps formulas simpler and less likely to throw an error :)

Best,

Lidiya Shutaya

lidiya@ddbconsultants.ca

JD J

Hi Lidiya!

Thank you so much for taking a crack at this.

I think the syntax you provided wasn't quite right but it was so close and led to me accidentally figuring out the solve!

=COUNTIFS({Case Log Range 1}, =DATE@row, {Case Log Range 2}, "Positive")

And you have to use the "," then click the row you want to reference... it can't have any parenthesis' around the internal sheet row reference.

I am now trying to figure out how to apply your second solve to be able to do a weekly summary but I am not quite clear on how to use the formula you have inputted.

Could you perhaps rephrase or expand on answer regarding the helper row?

Looking forward to your suggestions.

Lidiya S.

@JD J

Awesome that you were able to solve the first part of the problem!

No problem about rephrasing. On your daily sheet, you would add a column for "Week Number" and put the following formula in

=weeknumber(DATE@row)

This will look up the date and return which week of the year that date falls under.

The final step could be to sum the count using the following formula

=sumif(reference the week number column of your daily sheet,
=weekofnumber@row,reference the positive column in your daily sheet)

=sumif(criteria column, criteria, sum column)

Below is a screen shot of the way it would work.

Hope this helps! Please @ me if more clarification is needed or anything, always happy to help :)

Lidiya Shutaya

JD J

Hi,

While I was get the formula to work thanks @Lidiya Shutaya, while doing an audit to validate that the numbers are correct and I am noticing that sometimes the value returned is incorrect compared to when I do a manual spot check on a date. What could be causing this?

Note: I also noticed several of the values are off by 1 whereas others are off by more

I also expanded the formula to include additional criteria and I am so finding that the values return does not match what's in the reference sheet.

Formulas I am using:

=COUNTIFS({Case Log Range 1}, =DATE(2021, 8, 1), {Case Log Range 2}, "Positive")

Note: reference sheet is the same.

Any thoughts?

Thanks,

Lidiya S.

@JD J That is interesting, the only thing I could think of off the top of my head would be to check that the criteria does not have any syntax issues? Any chance you could share some more information with me or schedule 15 min and walk me through it?

I'm intrigued and here to help, so just send me a Google Meet/Teams/Zoom link.

Lidiya Shutaya

lidiya@ddbconsultants.ca

JD J

Hi @Lidiya Shutaya I would love your help.

Please join a call with me at:

Join Zoom Meeting

https://us06web.zoom.us/j/82753663010

Meeting ID: 827 5366 3010

Thanks!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.