Need help with a couple Formulas for On hold and Certain Counts

Jenny A.

First formula - In a sheet, in sheet summary I need a formula to count the instances certain Project Channels show up. There are three that will need to be counted. For now let's just call them Channel 1, Channel 2 and Channel 3. They are a dropdown column.

Second formula - same thing-same sheet and sheet summary formula. Those same three channels from the first one, I just need a count between those channels and the Project Status. The count that is needed - between those three project channels, and how many are not On Hold.

I tried a few different formulas, but cannot get them to work correctly. Thanks!

Paul Newcome

Try this:

=COUNTIFS([Project Channel]:[Project Channel], OR(HAS(@cell, "Channel 1"), HAS(@cell, "Channel 2"), HAS(@cell, "Channel 3")))

Paul Newcome

That's because you forgot to close out the OR function. The OR function is the "criteria" for the range right before it.

=COUNTIFS({Range}, OR(…………), {Another Range}, "Another Criteria")

Move one of the closing parenthesis from the very end to after "CHannel 3", and you should be good to go.

Do you have more than 1 status that could contain "On Hold"? If not, you can skip out on the NOT and CONTAINS function and just use

=COUNTIFS(………………………….., [Project Phase]:[Project Phase], @cell <> "On Hold")

That will cut down on parenthesis and whatnot and should aid in better sheet performance at a large scale.

Leibel S

@Jenny A.

I am not entirely clear on what you are looking for. But this should get you started:

the below formula would give you the total cells in the 'Channel' column that have "Channel 1" (as on of the) selected

=COUNTIFS([Channel]:[Channel],HAS(@cell, "Channel 1"))

Adam Murphy

For the first one try something like =COUNTIF([Project]:[Project], CONTAINS("Channel 1", @cell))

For the second one =COUNTIFS([Project]:[Project], CONTAINS("Channel 1", @cell), [Status]:[Status], NOT(CONTAINS("On Hold", @cell)))

Hope this helps!

Jenny A.

Thank you both!

I am having trouble adding multiple channels to count. I tried to add the extra ones below - as there are three channels I need to count (count how many times they show up in said column), but it is not working. Any help is appreciated. Once I have the first one figured out, the second one will be very similar. Thanks!!

=COUNTIF([Project Channel]:[Project Channel], CONTAINS("Channel 1", "Channel 2", "Channel 3", @cell))

Paul Newcome

Try this:

=COUNTIFS([Project Channel]:[Project Channel], OR(HAS(@cell, "Channel 1"), HAS(@cell, "Channel 2"), HAS(@cell, "Channel 3")))

Jenny A.

@Paul Newcome thank you so much! That worked.

My other formula I thought would work is not and I tried a couple things. I get a return of INVALID DATA TYPE. Thank you for any other help!

=COUNTIFS([Project Channel]:[Project Channel], OR(HAS(@cell, "Channel 1" ), HAS(@cell, "Channel 2" ), HAS(@cell, "Channel 3"), [Project Phase]:[Project Phase], NOT(CONTAINS("On Hold", @cell))))

Paul Newcome

That's because you forgot to close out the OR function. The OR function is the "criteria" for the range right before it.

=COUNTIFS({Range}, OR(…………), {Another Range}, "Another Criteria")

Move one of the closing parenthesis from the very end to after "CHannel 3", and you should be good to go.

Do you have more than 1 status that could contain "On Hold"? If not, you can skip out on the NOT and CONTAINS function and just use

=COUNTIFS(………………………….., [Project Phase]:[Project Phase], @cell <> "On Hold")

That will cut down on parenthesis and whatnot and should aid in better sheet performance at a large scale.

Jenny A.

@Paul Newcome thank you so much! I appreciate the help and was able to get them to work!

Paul Newcome

Happy to help. 👍️