Formula for Sum of # amount and Dollar Amount

Annie_B · 01/03/19

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"andForecasted ="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å · 01/03/19

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,[email protected])

Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Annie_B · 01/03/19

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 · 01/04/19

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

HealthForm 1 Form 2 Form 3 Form 4

GovForm 1 Form 2 Form 3 Form 4

OtherForm 1 Form 2 Form 3 Form 4

.

Form 1:

=COUNTIFS({Master Sheet Range 1},@cell=[email protected], {Master Sheet Range 2},@cell= 5)

.

Form 2:

=SUMIFS({Master Sheet Range 3}, {Master Sheet Range 1},@cell=[email protected], {Master Sheet Range 2},@cell= 5)

.

Form 3:

=COUNTIFS({Master Sheet Range 1},@cell=[email protected], {Master Sheet Range 2}, AND(NOT(ISBLANK(@cell)), NOT(@cell= 5)))

.

Form 4:

=SUMIFS({Master Sheet Range 3}, {Master Sheet Range 1},@cell=[email protected], {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 · 01/04/19

@rowISthe 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]@rowon row 1 is the same as [Column Name]1.

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

So on and so forth.

Annie_B · 01/04/19

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 · 01/04/19

Excellent! Happy to help.

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

Annie_B · 01/04/19

Really the only difference was the "@cell=[email protected]" 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=[email protected], {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 · 01/04/19

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.

不计算损失,你将包括它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 · 01/04/19

That worked perfectly! Thank you so so much!

Paul Newcome · 01/07/19

Excellent! Happy to help!

Formula for Sum of # amount and Dollar Amount

Comments

Help Article Resources

Categories