IF(AND) statement in Sheet Summary Field

Chris Bader · 12/23/20

Hello, I have searched and gotten close but no cigar yet!

I'm trying to create and IF,AND statement in a Sheet Summary field, (defined as Data Type: Text/Number).

I want one of two outputs in the Sheet Summary Field:

"Active" when a specific task name has Status <> Complete
"Complete" when the same specific task has Status = Complete.

My formula is: =IF(AND([Task Name]:[Task Name], "Go Live Successful", Status:Status, "Complete"), "Complete", "Active")

I'm getting the error: #INVALID DATA TYPE

The specific task I'm targeting is Go Live Successful:

I am putting this in the Sheet Summary so I can create a metric widget to count the number of active and completed projects in the dashboard.

Any advice or direction you can provide is greatly appreciated.

Thanks!

Chris

Kelly Moore · 01/01/21

Hey Chris

Happy New Year!

I believe the confusion is writing an IF formula for a summary, and writing an IF formula for a row. When you are calling the entire column [Task Name]:[Task Name], you have to make a comparison about the group.

To get a count of all your Actives and all your Completes, you need two summary fields, one for each category.

To gather your original request for Task Name = Go Live Successful and Status = Complete, your summary field is

=COUNTFS([Task Name]:[Task Name], "Go Live Successful", Status:Status, "Complete")

The 2nd summary field for Actives is:

=COUNTFS([Task Name]:[Task Name], "Go Live Successful", Status:Status, <>"Complete")

If you're only interested in the one task "Go Live Successful", then using the summary field is an easy way to gather the information for metrics. If you're interested in gathering this information for numerous tasks in your list, we may want to consider alternatives to summary fields.

Let me know, I'd be happy to help

Kelly

Kelly Moore · 12/24/20

Hi Chris

As written, the IF statement has collected some criteria but it hasn't compared it to anything. Are you wanting to know if any Go Live , Complete conditions exist in your range?

If yes, we can accomplish this with a Count function. If found the count would be greater than zero.

=IF(COUNT(AND([Task Name]:[Task Name], "Go Live Successful", Status:Status, "Complete")) > 0, "Go Live Successful", "Active")

Let me know more about what you're summarizing and I can help fine tune your formula.

Chris Bader · 12/24/20

Hi KDM.

Thanks for your quick response. That formula is returning "Go Live Successful" whether that specific task status is "Complete" or not "Complete"

What I'm after is for the Sheet Summary field called Project Status, to read "Active" if the task name [Go Live Successful] has status = Complete. If the status of that same task is <> Complete, then I'd like the Sheet Summary field to read "Active".

I really appreciate you taking time to help me with this. Thanks!

Kelly Moore · 12/25/20

Hi Chris

The Summary formula you wrote, looking at entire columns, has gathered a collection of data that meet certain criteria- let's say we collected 2,4,6,8. As written, you're asking us to call that collection true or false, which we can't do because we didn't say what we're judging true against. That's the part we need in your formula. As I wasn't sure what you wanted I gave you a formula which checks if any of the rows meet that criteria - it only takes 1 for that formula to be true, and the formula returns "Go Live Successful".

The formula below check if all the Go Live Rows in the [Task Name] column meet that criteria. I can't help but wonder though if you're more interested in designating Complete or Active at the individual row. We can do that if you add a column to put the formula in. Let me know if this is really what you want.

Try this formula and see if it works for you. If it doesn't, tell me exactly why it doesn't - which will define your evaluation criteria.

=IF(COUNTFS([Task Name]:[Task Name], "Go Live Successful", Status:Status, "Complete")=COUNT([Task Name]:[Task Name], "Complete"), "Go Live Successful", "Active")

Chris Bader · 01/01/21

Hi KDM and Happy New Year!

In my original summary formula, My formula is: =IF(AND([Task Name]:[Task Name], "Go Live Successful", Status:Status, "Complete"), "Complete", "Active")

My thought process was that if the task named "Go Live Successful" had the status "Complete", then the summary formula would return "Complete", otherwise it would return "Active" (which is what I was looking for if the formula was false.

In the formula from your above post, =IF(COUNTFS([Task Name]:[Task Name], "Go Live Successful", Status:Status, "Complete")=COUNT([Task Name]:[Task Name], "Complete"), "Go Live Successful", "Active") - I got an #UNPARSEABLE error.

I think another way to say what I'm after as that I want say that if a specific task has a specific status, then return the true value, otherwise return the false value.

I took a different tact that seems to be working well, but unfortunately, it is dependent on my calling out the specific task number (as opposed to a specific task name). I'm using: =IF(Status7 = "Not Started", "Not Started", IF(Status29 <> "Complete", "Active", "Complete"))

This is a little more robust and is meeting my needs. I think at this point, knowing if there is a way to accomplish this without having to call out the specific task number to be evaluated, but be able to make the evaluation based on the task name and the status of that specific task would be very helpful.

I really appreciate your guidance on this. Thanks!

Thanks, Chris

Kelly Moore · 01/01/21