Best Of

Hey @Cayla Davis !

Couple things, I would change your COUNTIF to COUNTIFS to allow for multiple if statements, specifically allowing you to break up your "if 'NDC' is found on another sheet" and "has Under Review, Pending Development or Pending Production Release" statements.

Then for your HAS statement (this assumes the {Sabre Traveler Care Priorities Range 1} is a dropdown/ multidrop down column, if not use CONTAINS instead), you will have to specify in that range that the cell will contain one of your options. Due to there being multiple options to select from, you will need to add OR prior to your has (Under Review, Pending Development or Pending Production Release.

Your formula should look like:

=COUNTIFS({Sabre Traveler Care Priorities Range 5}, "NDC", {Sabre Traveler Care Priorities Range 1}, OR(HAS(@cell, [Primary Column]2), (HAS(@cell, [Primary Column]3), (HAS(@cell, [Primary Column]4)))

Do you want rows 1-3 in the NDC column of your matrix pictured to have all NDCs for all 3 statuses (therefore all counts would be the same)? Or should NDC1 have NDC in "Under Review", NDC2 is NDC is Pending Development, NDC3 is Pending Production Release? Does Range 5 include NDC alone or within the cell?

If Range 5 has cells with only NDC and the Range 1 is a single select dropdown, maybe try:

=COUNTIFS({Range 5},"NDC",{Range 1}, Primary@row) - then make it a column formula

If Range 5 includes other text and you want to count any cell that contains NDC, and/or Range 1 is a multi-select dropdown, you could use CONTAINS in one or both criteria:

=COUNTIFS(CONTAINS({Range 5},"NDC"),CONTAINS({Range 1},"Under Review")) (replace "Under Review" with the other statuses for rows 2-3.

You just want a filter for the view, right? You can have multiple conditions in a filtered view.

Filtering for Tstart<=X AND Tend>=X seems like it should do the job for you for any value of X, they would just need to change the value of X for the week they want.

Hello, I was able to get both formulas to work after testing out the =SUM([Completed Count]:[Completed Count]). Thank you for your input.

@Jesse G. It did work! Thank you.

Hey @aneekahTAL

The syntax for your COUNTIFS is not correct. COUNTIFS have the syntax of =COUNTIFS({Range 1}, criteria 1, {Range 2}, criteria, {Range 3 etc}, criteria 3}. A COUNTIFS can also be used with only one Range-criteria pair.

=COUNTIFS({TPP Rollout - Hubs Range 2}; "A"; {State 9}; "Complete"; {TPP Rollout - Hubs Range 1}; "FY25")

Does this work for you?
Kelly

@Georgie ,

I figured it out!

I used this formula

=IFERROR(AVG(CHILDREN()),

IF(Status@row = "Complete", "1",

IF(Status@row = "In Progress", ".25",

IF(Status@row = "Blocked", ".5",

IF(Status@row = "Not Started", "0",

"")))))

Then I formatted the column with Percentage