Hi! Can someone please help me with writing a formula for my summary field?

Glenn Smith

I need to count rows if “Target Start” is between the Friday before last Friday and last Friday, “Work Type” is "EMERGENCY”, “Work Order” does not contain “IMP”, “Status” is not one of “Cancelled” or “Closed - Out of Scope”

I've tried:

=COUNTIFS({Target Start}, >=TODAY() - WEEKDAY(TODAY(), 2) - 2,{Target Start}, <=TODAY() - WEEKDAY(TODAY(), 2) + 1,{Work Type}, "EMERGENCY", {Work Order}, NOT(CONTAINS("IMP", {Work Order})), {Status}, NOT(OR("Cancelled", "Closed - Out of Scope")))

=COUNTIFS({Target Start}, >=TODAY() - WEEKDAY(TODAY(), 2) - 9, {Target Start}, <=TODAY() - WEEKDAY(TODAY(), 2) - 2, {Work Type}, "EMERGENCY"{Work Order}, NOT(CONTAINS("IMP", {Work Order})), {Status}, <> "Cancelled", {Status}, <> "Closed - Out of Scope")

=COUNTIFS({Target Start}, >=TODAY() - WEEKDAY(TODAY(), 2) - 9, {Target Start}, <=TODAY() - WEEKDAY(TODAY(), 2) - 2, {Work Type}, "EMERGENCY", {Work Order}, NOT(CONTAINS("IMP", {Work Order})), {Status}, <> "Cancelled", {Status}, <> "Closed - Out of Scope")

=COUNTIFS({Target Start}, >=TODAY() - WEEKDAY(TODAY(), 2) - 9, {Target Start}, <=TODAY() - WEEKDAY(TODAY(), 2) - 2, {Work Type}, "EMERGENCY", {Work Order}, NOT(CONTAINS("IMP", {Work Order})), {Status}, NOT(OR({Status} = "Cancelled", {Status} = "Closed - Out of Scope")))

=COUNTIFS({Target Start}, >=TODAY() - WEEKDAY(TODAY(), 2) - 9, {Target Start}, <=TODAY() - WEEKDAY(TODAY(), 2) - 2, {Work Type}, "EMERGENCY", {Work Order}, NOT(CONTAINS("IMP", {Work Order})), {Status}, NOT(OR({Status} = "Cancelled", {Status} = "Closed - Out of Scope")))

=COUNTIFS(([Target Start], >=TODAY() - WEEKDAY(TODAY(), 2) - 9), ([Target Start], <=TODAY() - WEEKDAY(TODAY(), 2) - 2), ([Work Type], "EMERGENCY"), ([Work Order], NOT(CONTAINS("IMP", [Work Order]))), ([Status], <> "Cancelled"), ([Status], <> "Closed - Out of Scope"))

SueinSpain

Without looking in great detail I find using a helper field helps to simplify the formula. For example a field called LAST WEEK with dropdown values of True, Flase. Then formula =IF([Target Start]@row >= TODAY(-6), "True", "False"). I appreciate this just gives the last rolling 7 days but….. you can play with this to fit your requirements to set True or False. then save it as a column formula so a value is always present

Then your next formula is simplified around the date part as you just [Last Week]:[Last Week], = "True"

Remember COUNTIFS the first field referenced is the field you want count and if blank it will not be counted.

I also think it is the NOT(Contains it does like as I couldn't get this to work.

This worked for me giving a result, as expected, of 2

Hope this helps and my general advice, provided by many others, is keep it as simple as possible

Glenn Smith

@SueinSpain Hi! Thanks for the reply. I am trying to create an automatically updating equation so I don't have to go in and put new dates in every week. I want to ultimately be able to open the summary field and see the number of rows populated.

I read that TODAY() - WEEKDAY(TODAY(), 2) - 9 will give you the Friday before last and TODAY() - WEEKDAY(TODAY(), 2) - 2 will give you last Friday.

When I work that into the equation, using the info you provided, I'm getting "#INVALID OPERATION"

=COUNTIFS([Target Start]:[Target Start], >=TODAY() - WEEKDAY(TODAY(), 2) - 9, [Target Start]:[Target Start], <=TODAY() - WEEKDAY(TODAY(), 2) - 2, [Work Type]:[Work Type], ="EMERGENCY", [Work Order]:[Work Order], <>"IMP", Status:Status, <>"Cancelled", Status:Status, <>"Closed - Out of Scope")

In the equation I provided in my question, I was receiving "#INVALID REF"

Glenn Smith

I need to gather the data that meets the requirements and is from Friday-Friday the previous week

SueinSpain

So as far as I know there is no fixed way of Friday to Friday other than determining which day you run it on and work backwards picking out a number of days to select between so if it was Monday you'd want < TODAY(-3) >= TODAY( -10) and flag this as in your select range by setting the True/False

However if you might run this on any day of the week and still need to go back Friday to Friday then maybe use a report to extract the data changing the dates to meet your criteria each time.

Sorry not to be more help