COUNTIFS or CONTAINS

MMorgan

Hello,

I'm trying to count the amount of projects a department has when another column is checked. The current formula I have keeps returning a "0" but I know there are four that need to be counted.

If Department is selected And Project is checked, what is the count?

=COUNTIFS({Projects}, true, {Supporting Department}, "Sales")

Not sure what I'm doing wrong, anyone have guidance?

Leibel S

What is the {Projects} column?

If you are note incorporating the {Projects} column anymore then the below would be your formula:

=COUNTIFS({Supporting Department}, HAS(@cell,"Sales"), {Status}, @cell="In Progress")

Leibel S

@MMorgan

Try this:

=COUNTIFS({Projects}, @cell=1, {Supporting Department}, @cell="Sales")

MMorgan

Thank you, but unfortunately, it is still returning a "0".

Kerry St. Thomas

I suggest you double-check the column types - make sure "Projects" = Checkbox and that "Restrict to Checkbox Use Only" is toggled on, that there's no data other than check/not-checked in that column, and that "Supporting Department" = Text/Number. Also, I recommend you double-check references by right-clicking on the sheet and choose "Manage References" - make sure you're pointing at the right thing. The formula I'd write is quite similar to what everyone has said so far:

=COUNTIFS({Projects}, 1, {Supporting Department}, "Sales")

Good luck!

MMorgan

Thank you for your response. The "Supporting Department" is a dropdown list as multiple departments are allowed to be selected. Is there a way to write this where only one specific supporting department is counted?

DKazatsky2

Hi @MMorgan,

Give this a try.

=COUNTIFS({Projects}, 1, {Supporting Department}, HAS(@cell, "Sales"))

Hope this helps,

Dave

MMorgan

Thank you. I tried that and it returned "0".

I did make a slight adjustment:

If "Supported Department" dropdown has a specific department selected, I want to count how many are in each status.

Here is what I tried with @DKazatsky2 's response:

=COUNTIFS({Supporting Department}, HAS("Sales", {Status}, "In Progress))

this returns a zero when there are projects that should be counted.

Thanks again for all the help!

Leibel S

@MMorgan maybe post a snapshot of your data with the rows you expect it to be counting.

Also, review your cross sheet references… are they set to the entire column?

MMorgan

Screenshot - Columns used.png

Here is a screen shot of the two columns I'm using.

Status is a dropdown where there can only be one option and Supporting Department there can be multiple options.

Let me know if that helps, thanks.

Leibel S

What is the {Projects} column?

If you are note incorporating the {Projects} column anymore then the below would be your formula:

=COUNTIFS({Supporting Department}, HAS(@cell,"Sales"), {Status}, @cell="In Progress")

MMorgan

That is it! Thank you!!!