Formula Help Please - Sheet Summary

ErinM

Hi everyone - I have a GIANT sheet I'm working with that contains our project portfolio. I need to add summary fields to capture Project Status (from a dop down field in a column) across 10 different Sponsor Divisions (also in a drop down field within a column).

I can't seem to crack this one. Can someone help? I'm sure it's a CountIF function...but...

thanks in advance!

Genevieve P.

Hi @ErinM

Don't give up! We can help!

Can you clarify if your dropdowns are multi-select or single select?

Instead of adding two COUNTIFS together, you would write it all in one COUNTIFS. IT sounds like your Status column is a single select, so there's no need to use CONTAINS.

Try this:

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Status:Status, "Submitted")

Then, yes! You can just swap bout "Submitted" to be your other values for the other counts.

Let us know if this worked for you! If not, I agree that screen captures would be helpful so we can check out your column set up, but please block out sensitive data.

Cheers,

Genevieve

GDHernandez

Hello,

I am by no means an expert, but I'm wondering if it would be something like this:

=COUNTIF([Column Name]:[Column Name], "Complete")

"Complete" would be whatever your status' are labeled.

ErinM

For what it's worth, this isn't working...

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell)) + COUNTIFS(Status:Status, CONTAINS("Approved", @cell))

Andrée Starå

Hi @ErinM

I hope you're well and safe!

That should work.

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Matt Johnson

Hi @ErinM

I apologize in advance for the long answer here:

If I follow/anticipate correctly, you would want each Sheet Summary Name to be each of the Sponsor Divisions and with the field be the options for status with the counts. If that's the case, below is a formula that would work with some tweaking obviously to use your data.

="Not Started - " + COUNTIFS([Sponsor Division]:[Sponsor Division], "EAST", [Project Status]:[Project Status], "Not Started") + CHAR(10) + "In Progress - " + COUNTIFS([Sponsor Division]:[Sponsor Division], "EAST", [Project Status]:[Project Status], "In Progress") + CHAR(10) + "Complete - " + COUNTIFS([Sponsor Division]:[Sponsor Division], "EAST", [Project Status]:[Project Status], "Complete") + CHAR(10)

Separately from Sheet Summary, it might be better to visualize it on a Dashboard. To do so you would create a new Sheet, maybe called Metric Sheet. Depending on how you want to see the data, what I'm describing below might need to change a little but...

You could list all the Sponsor Divisions in Column1, skipping the first row. Then add all the status types across the columns in Row 1. Next you'll add a COUNTIFS formula counting the other sheet against column 1 and row 1.

This would be good becuase you could drag the formula down and across and not have to edit each lookup value. Once that table is built you could display it with graphs and/or charts, or straight metric counts.

I hope that helps.

Matt

ErinM

Thanks for the replies. What's tricky is that I'm actually trying to build out summaries in the sheet to link to the Metrics Widget in a dashboard.

@Matt - I tried to do EXACTLY what you're saying with a Metrics sheet so that I could point all of my widgets to it. I failed. I'm not well versed at this yet...in particular creating a new sheet with data from others.

ErinM

also, @Matt Johnson - I think what you are summarizing is right. Your formula is the inverse of what I wrote? This formula gives me an incorrect value rather than an error:

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell)) + COUNTIFS(Status:Status, CONTAINS("submitted", @cell))

In my sheet, there are eleven values that start with "OneIT". There are 7 different statuses. I was hopeful to create 7 different summary fields that use the same formula but change out the "status name".

Maybe my @cell is throwing the error?

ErinM

Last attempt: =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT")) + Status:Status, ="Submitted")

Not working. :(

ErinM

Okay, for real, this is my last attempt:

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell)), IF([Status]:[Status]= ("Submitted, @cell))

I've tried it with COUNTIF and with COUNTIFS. I've tried it with and without @cell in it. I give up!

Genevieve P.

Hi @ErinM

Don't give up! We can help!

Can you clarify if your dropdowns are multi-select or single select?

Instead of adding two COUNTIFS together, you would write it all in one COUNTIFS. IT sounds like your Status column is a single select, so there's no need to use CONTAINS.

Try this:

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Status:Status, "Submitted")

Then, yes! You can just swap bout "Submitted" to be your other values for the other counts.

Let us know if this worked for you! If not, I agree that screen captures would be helpful so we can check out your column set up, but please block out sensitive data.

Cheers,

Genevieve

Andrée Starå

@ErinM

Can you share screenshots? That would help to figure out what's going on.

ErinM

That worked, @Genevieve P. - thank you so much!!! Both of the columns are drop downs with single select options only.

Oddly, when I tried to point at another column for a different value in a new summary field, I'm getting an error.

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Project Category:Project Category, "University/Division Initiative"). For some reason it's not picking up the Project Category column?

Genevieve P.

Hi @ErinM

I'm glad to hear it worked!

Any time a column name has a space or number you'll need to surround the name in [square brackets] so the formula knows when the column name starts and stops, like so:

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), [Project Category]:[Project Category], "University/Division Initiative")

Cheers,

Genevieve

ErinM

Aha, darn brackets! This worked:

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), [Project Category]:[Project Category], "University/Division Initiative")