COUNTIF FORMULA (3 Columns, 1 Column includes contact)

Jenny Schee

Hi All,

Need help with a formula!

I need a formula to summarize the following criteria. If Project Track = XX, and if Project Phase = XX and if Project Manager = X Person" what is the output?

My current formula, but it doesn't seem to be working. Is it due to the fact that the Project Manager filed is set to contact list?

=COUNTIFS([Project Track]:[Project Track], "T1-Concept", [Project Phase]:[Project Phase], "With Client", [Project Manager]:[Project Manager], "email address", [Project Track]:[Project Track], "T1-Concept", [Project Phase]:[Project Phase], "With Creative", [Project Manager]:[Project Manager], "email address")

Paul Newcome

I believe it is actually going to be an issue with the Project Phase portion. Based on your formula the Project Phase has to be both "With Client" and "With Creative" at the same time but only one of them which isn't possible. Are you trying to count if it is either one or the other? If so...

=COUNTIFS([Project Track]:[Project Track], "T1-Concept", [Project Phase]:[Project Phase], OR(@cell = "With Client", @cell = "With Creative"), [Project Manager]:[Project Manager], "email address")

Jenny Schee

Hey @Paul Newcome this formula would actually be for a sheet summary. Basically, the project track will always stay the same. But there are probably 15 or different project tracks. I need to be able to summarize those 15 which would be considered “active projects” within the drop down in project tracks there are 4 others options which are completed, cancelled thus I don’t need to count those. I just didn’t know what formula would be the best for my outcome!

Paul Newcome

I'm not sure I follow this part...

" within the drop down in project tracks there are 4 others options which are completed, cancelled thus I don’t need to count those"

Are you referring to the [Project Phase] column?

The above formula should work in a sheet summary field.

Jenny Schee

@Paul Newcome Sorry! Project Track column will always stay the same. But there are different variations within Project Phase. It can either be with client, with creative, with production, with PM, etc.

Jenny Schee

@Paul Newcome See

Paul Newcome

Ok. If you are trying to count all of them except for "Cancelled" and "Completed, then it would look like this...

=COUNTIFS([Project Track]:[Project Track], "T1-Concept", [Project Phase]:[Project Phase], OR(@cell <> "Cancelled", @cell <> "Completed"), [Project Manager]:[Project Manager], "email address")

If you have others, then you would include them in the OR statement using the @cell references.