Formula for Sum of # amount and Dollar Amount

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.

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Sheet Countdown Timer
Happy Thursday. I know many have asked this question before, but does anyone know of a way to have a column actively countdown time? Even if a plugin is needed, it will be considered. Here is what I'm looking to do. When a form is filled out and a new line is added to the sheet, I'd like the "Countdown Timer" column to…
Formula for populating date
I am needing a formula that calculates Termination Date (date property). The formula needs to look at Initiation Date (date property) and Term (text/num) which is based on # months, to calculate Termination Date. In this example, the termination date should return a value of 1/01/26. Thank you!
INCORRECT ARGUMENT SET on an =IF function
I'm trying to pull in the contact information of a second HR Business Partner, contingent on the department of the individual, from a separate reference sheet. However, not all departments have a second HRBP— so if the column of "second HRBP contact info" is blank, I don't want anything to be pulled into the new sheet's…