CountIfs Dates with ISBLANK Continuation

I am having trouble with my formula. I'm a newbie and my logic be flawed and terrible. Learning in progress. I thought I had received the answer but as it turns out I was still trying to acquire more information. Essentially what I want to do is run the calculation below:

=COUNTIFS({Action Plan Week Requested Date}, <=[1]1, {Action Plan Week Finish}, >=[1]1)

The only issues is that I have to create two new columns that give me the Numberweek Dates and I have to drag the formulas down.

Essentially I was condensing everything into one formula on a seperate sheet so that way I do not produce extra columns on the smartsheet. I used the formula below but it did not work as I thought it would.

=COUNTIFS(Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1) + COUNTIFS({Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, @cell = "", {Action Plan Finish Date}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1)

I'm basically doing a rolling count of an item from when it opens until it closes (ie. has a Plan Finish or Actual Finish Date). For Instance if the request date is week 3 then it count for week 4, week 5, week 6, etc. until the Plan finish or actual finish.

Find more posts tagged with

Accepted answers

All comments

RossL

@Rigoberto Huizar

What do you want the formula to return if there is an error? The way this is written the value to return if it comes back with an error is another logical test and not a value.

Rigoberto Huizar

@RossL

ahh. I see. I'm a pretty green with smarthsheet. Essentially what I'm trying to do is the following:

I'm basically doing a rolling count of an item from when it opens until it closes (ie. has a Plan Finish or Actual Finish Date). For Instance if the request date is week 1 then it counts for week 2, week 3, week 4, etc. until the Plan finish Date or actual finish date which can be week 12. The tasks or item would count from week 1 through week 12 based on start and finish.

For instance: I look for the start date of a task/item, then look at plan finish date or actual finish date (if the Actual finish is blank then I use the plan finish Date. Thus: COUNTIFS({Action Plan Week Requested Date}, <=[1]1, {Action Plan Week Finish}, >=[1]1), essentially showing the count of task/items for week 1.

Based on this formula: COUNTIFS({Action Plan Week Requested Date}, <=[1]1, {Action Plan Week Finish}, >=[1]1) it seems to work but I have to create extra columns to create NumWeek to get the count.

I was trying to eliminate the creating of extra columns on the Main Smartsheet and do the calculation on another sheet and make a widget out of it.

I hope this makes sense.

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.