SUM formulas for a Dashboard View

slando

Hello SMEs,

I am putting together an Exec. Dashboard where I have created my "calculation sheet" from my few main sheets, and I am trying to create a formula that can SUM up and display on a Pi Chart Widget on my dashboard, the following

COLUMNS

1- Client Status (has a defined list of dropped down values - WON, LOST, IN PROGRESS, DELAYED, SIGNED)

2- Expected Revenue $ - each of the above statuses has a different value $) depending on how we scope the project.

I can/ and have, easily SUMMED up the entire column to my "calculation sheet" as a full column and it shows the FULL EXPECTED REVENUE for ALL the statuses listed, not by each though.

What I am trying to do , and having trouble with, is in a separate column on my calc. sheet, is create a formula that SUMS up the total "DELAYED" (and each of the statuses listed as well) values from the entire column along with the total Expected revenue $ for only the DELAYED rows, so that in my Pie Chart, I can show the total expected Rev. for each of the Expected Statuses that are scoped.

Any thoughts??

Sean

Genevieve P.

Hey @slando

One quick and easy way to do this would be to create a Report, Grouping by the Status column and using the Summary feature in a Report to SUM the Expected Revenue cost. Here's a free webinar that goes through these functions: Redesigned Reports with Grouping and Summary Functions. Charts can use the first grouping/summary in a Report as their source.

If you'd prefer to use a formula, you'd want to use SUMIFS like so:

=SUMIFS({Column to SUM}, {Status Column}, "Status to look for")

So in your case, something like:

=SUMIFS({Expected Revenue Reference}, {Status Reference}, "Delayed")

See: Create cross sheet references to work with data in another sheet

Cheers,

Genevieve