How do you get status counts by department?

Johnna Van

In my project plan I have children rows that have different status. I've created another sheet/grid and trying to reference my main sheet and only pull in the count of status by department. I've reviewed other solutions but coming up 'unparseable'. I have the total counts of each status but need broken down by department. Attached is my sample set up.

Main sample project plan

Separate grid where I want to reference the above sheet and get counts by status by department

I've have the formula for the overall counts but struggling how I get it by department. My formula for overall is: =COUNTIF({Sheet Ref Range 1}, "Open")

I appreciate any help.

Cheers,

Johnna

Genevieve P.

Hi @Johnna Van

The COUNTIFS formula evaluates rows on an individual basis, so you would need to have the Parent (Dept) and other Parent (Brand) content on the same row as the Child rows.

What I would do here is add a helper column called "Parent Names" or something similar. Then you can use a JOIN(ANCESTORS formula to bring back both hierarchy levels, like so:

=JOIN(ANCESTORS(Department@row), " - ")

Then you can use your formulas, looking at this helper column to determine Brand and Dept.

=COUNTIFS({Status Column}, "Open", {Parent Names}, "Brand 1 - DEPT 1")

You could set up the same JOIN(ANCESTORS formula in your metric sheet (and even hide this column) to reference in your cross sheet formula:

=COUNTIFS({Status Column}, "Open", {Parent Names}, [Parent Names]@row)

This will keep it unique so you're combining the Dept. with the Brand - otherwise you may get Counts for the same "Dept" text if it shows up under multiple brands.

Cheers,

Genevieve

Genevieve P.

Hi @Johnna Van

The COUNTIF formula is evaluating the source sheet on a row-by-row basis, meaning that unless the parent details exist on the child rows it won't be able to tell what Department it's indented underneath.

What I would personally do here is add a helper column in your source sheet (you can hide this column so it doesn't show, after you've set it up). Then you can use the PARENT Function to automatically bring the Parent name into the Child rows, like so:

=PARENT(Department@row)

You can make this a column formula. Then in your second sheet, adjust the COUNTIF to be a COUNTIFS, plural, and include the new helper column and criteria like so:

=COUNTIFS({Sheet Ref Range 1}, "Open", {Sheet Ref Helper Column}, [Primary Column]@row)

Let me know if you would like to see screen capture examples and I'd be happy to help further!

Cheers,

Genevieve

Johnna Van

@Genevieve P. if you could provide screen shots that would be helpful. Thanks so much!

Genevieve P.

Hi @Johnna Van

No problem!

Here's the helper formula in my Source Sheet:

=PARENT(Department@row)

Notice how now it says "DEPT 1" next to every Child Row that is under "DEPT 1". Now I can reference this helper column in my other formula:

=COUNTIFS({Sheet Ref Range 1}, "Open", {Sheet Ref Helper Column}, [Primary Column]@row)

Cheers,

Genevieve

Johnna Van

@Genevieve P. EXCELLENT...that is BEAUTIFUL! I was hoping this would also help me with another sheet but I"m stuck. Would I do the same type of process for a helper column? I have a separate sheet and I'm trying to reference the main sheet in getting the count of open items by assignee. I've tried a few different ways but not coming up successful.

Thank you so much for your help!!

Genevieve P.

Hi @Johnna Van

Glad I could help! 🙂

For your next question, what formula are you currently using? Is the assignee column multi-select or single select?

Johnna Van

@Genevieve P. I've tried so many...assignee is single select. This is the latest one: =COUNTIF({Assignee Column}, "Joe"), [Status], "Open". I haven't added a new helper column and struggling to get both assignee and status formula.

Genevieve P.

Hey @Johnna Van

Thanks for clarifying, this helps a lot! A few things to adjust, here.

Firstly, as soon as we have more than one criteria (an assignee and... something) we'll want to use COUNTIFS < plural.

Secondly, we want to keep the function "open" until all of the ranges and criteria are listed. What I mean by this is the closing parentheses at the end:

COUNTIFS(.....) < this

So in your case, this ) will appear after "Open".

Finally, we'll want to make sure that the {ranges} listed are both {cross sheet references} that look into the correct columns. See: Create cross sheet references to work with data in another sheet

Try a formula structure like this:

=COUNTIFS({Assignee Column}, "Joe", {Status Column}, "Open")

If this hasn't helped, it would be useful to see a screen capture of your two sheets like you did in the original post, but please block out sensitive data.

Thanks!

Genevieve

Johnna Van

@Genevieve P. thank you but I'm not sure what to reference for the main sheet.

Main Sheet:

Separate sheet where I want to reference the main sheet's data:

Do I need to create another helper column in the main sheet?

Thanks so much!

Johnna Van

@Genevieve P. I got it. I see how I was able to reference the main sheet for both columns. AHH thanks so much. Have a great rest of your week and weekend ahead.

Genevieve P.

So glad to hear you got it working, @Johnna Van!

Just a note - based on your screen captures you could actually reference the cell in the "Assignee" column instead of needing to type in "Joe" in your formula:

=COUNTIFS({Assignee Column}, Assignee@row, {Status Column}, "Open")

That way you can drag the formula down your column and it will automatically update based on who is in that column to the left!

I hope you have a great rest of your week as well 🙂

Genevieve

Johnna Van

@Genevieve P. GREAT, thank you!

Johnna Van

@Genevieve P. can you help me with one more formula? I'm trying to get the count by status by department by brand. I've tried using the above formulas and adding another reference but coming up short. What I've tried: =COUNTIFS({Status}, "Open", {Department}, "Dept 1", {Brand}, "Brand 1")

Main Sheet:

Separate Sheet where I want to reference the data:

Cheers,

Johnna

Genevieve P.

Hi @Johnna Van

The COUNTIFS formula evaluates rows on an individual basis, so you would need to have the Parent (Dept) and other Parent (Brand) content on the same row as the Child rows.

What I would do here is add a helper column called "Parent Names" or something similar. Then you can use a JOIN(ANCESTORS formula to bring back both hierarchy levels, like so:

=JOIN(ANCESTORS(Department@row), " - ")

Then you can use your formulas, looking at this helper column to determine Brand and Dept.

=COUNTIFS({Status Column}, "Open", {Parent Names}, "Brand 1 - DEPT 1")

You could set up the same JOIN(ANCESTORS formula in your metric sheet (and even hide this column) to reference in your cross sheet formula:

=COUNTIFS({Status Column}, "Open", {Parent Names}, [Parent Names]@row)

This will keep it unique so you're combining the Dept. with the Brand - otherwise you may get Counts for the same "Dept" text if it shows up under multiple brands.

Cheers,

Genevieve

Johnna Van

@Genevieve P. OMG this is EXCELLENT. Thank you!