# 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 :)

• ✭✭✭✭✭✭

Are you looking for a count of these rows?

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

• ✭✭✭✭✭✭

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()))

• 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?

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

• ✭✭✭✭✭✭

In each of the {Cross Sheet References}.

.

• 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…;(

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

• ✭✭✭✭✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!