How do you get status counts by department?

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

All comments

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

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

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

@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!!

Glad I could help! 🙂

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

@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.

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

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

Main Sheet:

Screen Shot 2022-11-17 at 7.57.21 AM.png

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

Screen Shot 2022-11-17 at 7.58.51 AM.png

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

Thanks so much!

@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.

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

@Genevieve P. GREAT, thank you!

@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:

Screen Shot 2022-11-28 at 11.07.16 AM.png

Separate Sheet where I want to reference the data:

Screen Shot 2022-11-28 at 11.11.05 AM.png

Cheers,

Johnna

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