Formula for Sum of # amount and Dollar Amount

Annie_B

I am trying to create a Dashboard to show two things:

#1- To show how many (#) of each industry that are “wins” and how many (#) of each industry that are “forecasted”.  The columns are titled "Industry" and the "Stage" column has the information for Wins and Forecasted.

Wins= "5 - Win" and Forecasted = "0 - Lead", "1 - Unqualified" , "2 - Qualified" , "3 - Proposal" , "4 - Final (EL), "8 - Hold" 

 

#2 -to show the dollar amount ($) for the same industries that are “wins” and “forecasted”. Same as above, but the dollar amount is in the "Forecasted Amount" column. 

Andrée Starå

Hi Annie,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Annie_B

Thank you for your help, I will attach a screenshot below for reference.  You can see the columns that will be used highlighted in yellow.

We would like to create a dashboard to show each Industry leader how many opportunities and how much money they are bringing in. 

The Stage column is what we need to sort the wins from the forecasted.

For Example: We would have one widget that shows the Healthcare Industry as having 4 Wins (a win is defined on the Stage column as "5 - Win") and 8 Forecasted Opportunites (Forecasted is defined on the Stage column as "0 - Lead", "1 - Unqualified" , "2 - Qualified" , "3 - Proposal" , "4 - Final (EL) and "8 - Hold" on the Stage column).

And another widget that shows the Healthcare Industry as having brought in "$X" of wins and "$X" of forecasted opportunities (same as above but with the dollar amount rather than the number of opportunities). 

We would like to do this for each industry.

Paul Newcome

To display this information on a dashboard, you will need to compile it in a sheet first. I will keep this example short and assume that the data is being compiled on a separate sheet.

.

.

Master Sheet:

Stage          Industry          Forecasted Amount

1                  Health              $1,000.00

5                  Gov                  $5,000.00

3                  Other                $0.00

.

.

Summary Sheet:

Industry     Won Count     Won Amount     Opp Count     Opp Amount

Health         Form 1             Form 2                Form 3            Form 4

Gov             Form 1             Form 2                Form 3            Form 4

Other          Form 1             Form 2                Form 3            Form 4

.

.

Form 1:

=COUNTIFS({Master Sheet Range 1}, @cell = Industry@row, {Master Sheet Range 2}, @cell = 5)

.

Form 2:

=SUMIFS({Master Sheet Range 3}, {Master Sheet Range 1}, @cell = Industry@row, {Master Sheet Range 2}, @cell = 5)

.

Form 3:

=COUNTIFS({Master Sheet Range 1}, @cell = Industry@row, {Master Sheet Range 2}, AND(NOT(ISBLANK(@cell)), NOT(@cell = 5)))

.

Form 4:

=SUMIFS({Master Sheet Range 3}, {Master Sheet Range 1}, @cell = Industry@row, {Master Sheet Range 2}, AND(NOT(ISBLANK(@cell)), NOT(@cell = 5)))

.

.

{Master Sheet Range 1}: Industry Column on Master Sheet

{Master Sheet Range 2}: Stage Column on Master Sheet

{Master Sheet Range 3}: Forecasted Amount Column on Master Sheet

.

.

From here, you can reference the summary sheet in your widgets to display the compiled data. Let me know if you need any further clarification or help or if this doesn't work for you.

Paul Newcome

@row IS the row reference. It replaces the row number and tells the formula to just look in that column for whatever the row is on.

 

[Column Name]@row on row 1 is the same as [Column Name]1.

[Column Name]@row on row 2 is the same as [Column Name]2.

 

So on and so forth.

Annie_B

Thank you, Thank you, Thank you! I got it to work! I did have to change the formula just a bit but it all looks great.  I can't thank you enough!

Paul Newcome

Excellent! Happy to help. 

 

Out of curiosity... What needed changed (other than column names and sheet references)?

Annie_B

Really the only difference was the "@cell = Industry@row" part.  I wasn't able to get the "@row" part to work, so I just didn't use it and changed it to " @cell = "Industry" " and it seems to work.  I've pasted below the full formula for reference.

Given Formula 

=SUMIFS({Master Sheet Range 3}, {Master Sheet Range 1}, @cell = Industry@row, {Master Sheet Range 2}, AND(NOT(ISBLANK(@cell)), NOT(@cell = 5)))

My Formula

=SUMIFS({RACS Business Development Range 4}, {RACS Business Development Range 1}, @cell = "Closely Held Business", {RACS Business Development Range 2}, AND(NOT(ISBLANK(@cell)), NOT(@cell = "5 - Win")))

 

One final question as well: How can I also make this NOT calculate the losses? For instance, the formula below has NOT(@cell = "5 - Win") but I also need it to not calculate the "6 - Loss" .

Paul Newcome

To make the given formula work, the text in the Industry column would have to match EXACTLY on both sheets. Spaces, punctuation and upper/lower case. I don't know if that may have been the issue or not.

 

To not calculate the losses, you would include it in the AND statement in the same manner as the ISBLANK and the 5, so starting with AND, enter...

 

AND(NOT(ISBLANK(@cell)), NOT(@cell = "5 - Win"), NOT(@cell = "6 - Loss")))

Annie_B

That worked perfectly! Thank you so so much!

Paul Newcome

Excellent! Happy to help!