I have a general table I use to keep track of goals for a sales team. I think they're relatively simple, but I'm having a heck of a time trying to get them into SS.
The things I need to track:
To complicate matters, I have 2 types of goals I need to track. One we are calling "Flat Fees" which is exactly what it sounds like, a fee we are paid, and the other is "CPAi" which is also tracked in $.
Attached what my normal excel sheets look like. Need to figure out how to make that in SS if anyone can help.
Hey
To get the values you need we're going to use the SUMIFS function. The syntax of the SUMIFS is (Range to be summed, Criteria range1, criteria1, criteria range2, criteria2, criteria range3, criteria3, etc etc). You always have to have a criteria range-criteria pair, even if the criteria range has already been listed. SUMIFS can be used in both same sheet formulas as well as cross-sheet references.
As a reminder, since we are referencing another sheet, which makes this a Cross Sheet Reference, you cannot simply copy-paste this formula into a cell. You have to create each reference (whatever is in the curly brackets) individually. Each reference in the formula below is a single column - I encourage you to rename the generic range name smartsheet creates to the actual sheet name column name - it is a good practice always. You'll see from my references I renamed it so I could help you see exactly what column was needed. If you need a refresher on creating cross sheet references, please see this link
Here is the formula for Q1 Flat Fee
=SUMIFS({Source Sheet Revenue Value}, {Source Sheet Quarter}, "Q1", {Source Sheet Placement Year}, 2021, {Source Sheet Revenue}, "Flat Fee")
Here is the formula for Q1 CPAi
=SUMIFS({Source Sheet Expected CPA Revenue}, {Source Sheet Quarter}, "Q1", {Source Sheet Placement Year}, 2021, {Source Sheet Revenue}, "CPAi")
If you are planning on creating a dashboard and show the quarterly data in a chart, reach out if you need any help.
cheers
Kelly
Hey
It looks like you're missing a quote sign after Hybrid.
cheers
Kelly
If I understand correctly, you wish to create a sheet to track these metrics. You mentioned you were having trouble creating this in smartsheet. Is smartsheet producing errors? If yes, what error(s) is presented?
You should be able to create a replicate of this sheet. With these few of cells it is probably easiest to manually re type into a Smartsheet Grid (aka spreadsheet). If the cells you show are linked to other sheets, let us know and we can help with formulas. Smartsheet doesn't have multiple worksheets (tabs) existing as a single workbook file.
If you wanted to import a worksheet from excel, you can click the FILE , CREATE NEW, IMPORT and select excel. You might find this information useful.
If you help me better understand where you have difficulty I'll be happy to try to give more specific advice on creating the sheet. (This assumes you have a smartsheet license, as only licensed users can create new sheets)
cheers,
Kelly
Yes, I’ve got the sheet uploaded to Smartsheet but I can’t figure out how to do the formulas. Here’s what’s needed:
Hey Tiara
Is it possible for a screenshot of the other sheet (or a mocked up version if data is sensitive). I'm wanting to see how the data is actually arranged and what data you have by row. Or, if you have the excel version with formula, what excel formula did you use.
We will almost certainly be using Cross Sheet References. As a good practice when using Cross Sheet References, always change the name of the default range to reflect the sheet and column name being referenced. Besides helping you read your formulas, it tremendously helps the community understand your formulas.
I need to understand the other sheet data before we can come up with the formula for the QTD Sales
For the [% Goal to Target], enter this in the [% Goal to Target] cell on the Flat Fee row
= [Actual QTD]@row/Target@row
with no value yet in the [Actual QTD]@row this will show an error.
thank you!! The % formula worked perfectly. So for the other question I had, here's what I need to reference:
For [Actualy QTD] in the Flat Fee row, it needs to reference anything in Q1 of 2021 with Flat Fee as the Revenue Value. For [Actual QTD] in the CPAi row, it needs to reference anything in Q1 of 2021 with CPA as the Revenue Value. Here's some examples of where those live:
Quarter and Placement Year:
Flat Fee and Revenue Value:
CPA and Expected CPA Revenue:
Does that make sense and can you help me with a formula based on that?
Perfect. This is just what I needed. I'll have it later tonight. You mentioned that formula was just for Q1. What will you do for Q2, Q3, etc? Will you replace the current values in this same column with the current quarter, or will you add Q1+Q2, for example. Have you considered separate columns for each quarter? The separate columns are what I would suggest -this would give you the option of quickly comparing the quarters or summing them for the YTD values.
Kelly
Great idea, Kelly! Yes, I can definitely add new columns for all the quarters. I was anticipating I would simply change the quarter in the formula and update but your way is easier. :) thank you!!
Hey
To get the values you need we're going to use the SUMIFS function. The syntax of the SUMIFS is (Range to be summed, Criteria range1, criteria1, criteria range2, criteria2, criteria range3, criteria3, etc etc). You always have to have a criteria range-criteria pair, even if the criteria range has already been listed. SUMIFS can be used in both same sheet formulas as well as cross-sheet references.
As a reminder, since we are referencing another sheet, which makes this a Cross Sheet Reference, you cannot simply copy-paste this formula into a cell. You have to create each reference (whatever is in the curly brackets) individually. Each reference in the formula below is a single column - I encourage you to rename the generic range name smartsheet creates to the actual sheet name column name - it is a good practice always. You'll see from my references I renamed it so I could help you see exactly what column was needed. If you need a refresher on creating cross sheet references, please see this link
Here is the formula for Q1 Flat Fee
=SUMIFS({Source Sheet Revenue Value}, {Source Sheet Quarter}, "Q1", {Source Sheet Placement Year}, 2021, {Source Sheet Revenue}, "Flat Fee")
Here is the formula for Q1 CPAi
=SUMIFS({Source Sheet Expected CPA Revenue}, {Source Sheet Quarter}, "Q1", {Source Sheet Placement Year}, 2021, {Source Sheet Revenue}, "CPAi")
If you are planning on creating a dashboard and show the quarterly data in a chart, reach out if you need any help.
cheers
Kelly
THANK YOU!!! One last question. If I want to reference 2 different criterion in the "Revenue" column, how can I do that within this formula?
=SUMIFS({FF Rev Val}, {Paid Placements Range 1}, "Q1", {Paid Placements Range 2}, 2021, {Paid Placements Range 3}, "Flat Fee")
I had forgotten we sometimes use a 3rd Revenue type, Hybrid. I will need to reference that for the SUM in both Flat Fee and CPAi
Looking at your formula (you didn't rename your ranges so I can tell what column they refer to- consider doing it in your next formula as a good practice) - are you saying that your 'range 3' it can be either "Flat Fee" or "Hybrid", and the same in the CPAi?
If yes,
=SUMIFS({FF Rev Val}, {Paid Placements Range 1}, "Q1", {Paid Placements Range 2}, 2021, {Paid Placements Range 3}, OR(@cell="Flat Fee", @cell="Hybrid))
You'll do the same in your CPAi formula
kelly
I can't seem to get that formula to work. It says UNPARSEABLE.
Thanks for that tip btw! I just renamed them for ease of looking at the formulas. Always forget that since I'm in a rush. So here's the formula I'm using:
=SUMIFS({FF Rev Val}, {Quarter}, "Q1", {Year}, 2021, {Rev Type}, OR(@cell="Flat Fee", @cell="Hybrid))
Hey
It looks like you're missing a quote sign after Hybrid.
cheers
Kelly
Thanks so much, Kelly!!! This worked perfectly. I appreciate all the help :)
