Formula: calculate percentage based on certain criteria

Desire

Hi, please can you assist?

I have 2 worksheets, called "Position Requisition" and "Dashboard Data".

In the "Position Requisition" I have two columns, called "Total Train ATTR" and "Location"

In the "Dashboard Data" I have two columns, called "Primary Column" and "Totals"

I want to add up all the totals in the "Total Train ATTR" column that are associated with a location in the "Location" column and I want that total displayed as a percentage in my "Dashboard Data" sheet.

So if I have 6 Asheville's in "Locations" that totals up to 0.69 in "Total Train ATTR", then I want that to display 11.5% in my "Dashboard Data" sheet.

Genevieve P.

Hi @Desire

You can use a SUMIFS Function to look into a source sheet and sum values based on a criteria. For example, you could write something like this in your Dashboard Data "Totals" column:

=SUMIFS({Total Train ATTR column}, {Location Column}, [Primary Column]@row)

You'll want to create two cross-sheet {references} to look at your two columns in the other sheet, see: Create cross sheet references to work with data in another sheet

Then once you have all the totals, you can use this Dashboard Data sheet as a source for a Pie Chart in your Dashboard. This will automatically create a percentage based on the values for each row compared to the total of all rows.

Let me know if that works for you!

Cheers,

Genevieve

Desire

Hi @Genevieve P.

Thank you so much. I had the correct formula, I just didn't think of using a Pie Chart, it works perfectly, thank you.

Genevieve P.

No problem! Glad I could help 🙂