Formula Needed for Calculation Sheet

I need a formula for a Calculation Sheet I use for Dashboards:

Main Sheet Name: Migration Grid 03.29.23

Columns Used: "Type", "IDENT $ - PAH (ATL)", "IMP $ - PAH (ATL)", "VALID $ - PAH (ATL), "Status", "Actual IMP Date",

Type Column must EXCLUDE: "Optimization", "Cost Avoidance"

Status Column must EXCLUDE: "HOLD", "DROPPED", "Pipeline"

Actual IMP Date: must INCLUDE Date Range from 12/1/23 to current date

Formula Needed:

I need to see data from Sheet MIGRATION GRID 03.29.23 for initiatives with IDENT $, IMP $, and VALID $ for each facility for the time period of 12/01 - current, that excludes status that are HOLD, DROPPED or PIPELINE, and also EXCLUDES those with a "Type" that excludes OPTIMIZATION and COST AVOIDANCE

Let me know if anything needs clarification @Paul Newcome :)

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you looking for a count of these rows?

Not so formula savvy

I need the total Sum for the rows for each column category (Identified $ etc.)…;)

Paul Newcome

In that case you would use a SUMIFS with a couple of AND functions. I believe it would be similar to other formulas we have put together.

=SUMIFS({Range To Sum}, {Status Range}, @cell <> "This", @cell <> "That"), {Type Range}, AND(@cell <> "Something", @cell <> "Something Else"), {Date Range}, AND(@cell >= DATE(2023, 12, 1), @cell <= TODAY()))

Not so formula savvy

How do I make sure that particular sheet is what is being referenced in the formula is where I get tied up? since this is not a formula within that sheet but a separate sheet?

Not so formula savvy

where in this formula do i reference that specific sheet "Migration Grid 03.29.23"?

Paul Newcome

In each of the {Cross Sheet References}.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Not so formula savvy

I am not following?…All of the columns all belong to one sheet (Migration Grid 03.29.23)….I am trying to follow the directions but having a difficult time…;(

Not so formula savvy

I am revisiting this need to see if i can get the same data via pivots. thanks Paul!

Paul Newcome

Are you putting the formula for the sum in the same sheet as the source data or a different sheet?

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.