COUNTIF referencing another sheet

Shannon E.

I am needing to count the number of rows that are classified as rolling deadlines. The data I am trying to reference is in a separate sheet. This is the formula I currently have that is giving me the #UNPARSEABLE error.

=COUNTIF({Proposal Tracker Range 1}[Deadline Type]:[Deadline Type], "Rolling Deadline")

Please help me understand the errors I am making.

Thank you

Paul Newcome

When using a cross sheet reference, you create the reference to look at the range you want it to cover. This means you do not have to separately spell out which column you want it to look at.

=COUNTIF({Proposal Tracker Range 1}, "Rolling Deadline")

Paul Newcome

When using a cross sheet reference, you create the reference to look at the range you want it to cover. This means you do not have to separately spell out which column you want it to look at.

=COUNTIF({Proposal Tracker Range 1}, "Rolling Deadline")

Shannon E.

@Paul Newcome Thank you. That worked.

Paul Newcome

Happy to help. 👍️

Shannon E.

@Paul Newcome I now need to count the number of rows that have "Research and Development" and "Pre-Award" for the Review Type. This is the formula I have currently that is not working.

=COUNTIFS({Proposal Tracker Range 5}, "Research and Development", [{Proposal Tracker Range 5}, "Pre-Award"])

I need this formula to work so that it will count the number of cells without being dependent on the order of wording. Some cells have Research and Development, then Pre-Award; while others have Pre-Award then Research Development.

I appreciate your help.

Paul Newcome

@Shannon E. You would need to remove the square brackets from the formula.

I don't think though that the formula would still get what you want. What you actually need is a combination of OR and CONTAINS.

=COUNTIFS({Range}, OR(CONTAINS("ABC", @cell), CONTAINS("DEF", @cell)))

Shannon E.

@Paul Newcome

=COUNTIFS({{Proposal Tracker Range 5}, OR(CONTAINS("Research and Development", @cell), CONTAINS("Pre-Award", @cell)))

Gives me a count of 33, but it should be 11

Shannon E.

@Paul Newcome

Found it. It should be an AND statement instead of OR.

=COUNTIFS({Proposal Tracker Range 5}, AND(CONTAINS("Research and Development", @cell), CONTAINS("Pre-Award", @cell)))

Paul Newcome

My apologies. I misunderstood what you were looking for. Glad you got it figured out.

Shannon E.

@Paul Newcome No worries. I appreciate your continuous feedback and guidance.

Paul Newcome

Happy to help. 👍️