How do you get status counts by department?

Johnna Van
Johnna Van ✭✭✭
edited 11/15/22 in Formulas and Functions

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

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Johnna Van
    Johnna Van ✭✭✭

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

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Johnna Van
    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!!

  • 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?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Johnna Van
    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.

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Johnna Van
    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
    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.

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Johnna Van
    Johnna Van ✭✭✭
    edited 11/28/22

    @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.
    Genevieve P. Employee
    Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Johnna Van
    Johnna Van ✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!