Formula HELP - Trying to total cells that belong to specific market

I have tried several countif and sumif formulas but am at a loss. I have an expense report that has a market column (think departments) and a Project Costs column (total paid for the project). There are multiple markets to select from. I am trying to get a running total of project costs for each market. In simple form, my formula would be:

Total "Project Costs" for Market "General (MarComm)"

Not sure if I need a SumIF formula or a CountIF formula. Sorry, I am horrible at formulas.

I need to create a bunch of these for the different Markets to display on a dashboard.

Find more posts tagged with

Formulas

Comments

Paul Newcome

My suggestion would be to create a metrics sheet with each market listed only once similar to (column headers in bold):

Market Total

A f

Where you see the "f", enter this:

=SUMIFS({Project Costs}, {Market}, Market@row)

{Project Costs}: Follow the appropriate steps for cross sheet references and select the cost column you want to sum.

{Market}: Follow the appropriate steps for cross sheet references and select the column housing the market name.

Market@row: Leave as is.

You can then dragfill the formula on down the Total column, and it will automatically update itself to sum all of the values where Market matches.

Andrea Prochaska

OMG - you saved me many more hours of banging my head on my desk!! I keep using "(" instead of "{" Why use one over the other?

Is there also a way to take it one step further...

I want to also add (2) criteria and then sum if possible. I have tried the following:

=SUMIFS({Project Costs}, {GL Acct No.}, {Market}, Market@row)

So if Market is X and then has an expense of say "advertising" is would sum those expenses. I tried the above, but it doesn't seem to work. However, I have some "weird" column names that all have spaces. I have tried the following using my actual column names:

=SUMIFS({PSS Expenses for 2019 Range 2}, {PSS Expenses for 2019 Range 3}, (Advertising - 770100)}, {PSS Expenses for 2019 Range 1}, (General (MarComm))

Range 2 = Project Cost column

Range 3 = Finance account

Range 1 = Market column

Paul Newcome

( ) vs [ ] vs { } all depends on your use.

( ) is used to open and close a function or to encase a specific portion of a calculation to indicate it needs done first.

=MAX(.....) Opens and closes the MAX function

=(5 + 1) / 6 Indicates that the calculation of 5 + 1 needs to be done before dividing by 6.

[ ] is used to encase column names that have spaces, special characters, numbers, or a combination thereof.

If your column name is "ColumnTwo", it would not need square brackets because it is all text with no spaces, but if it were "Column Two" or "Column2" or "Column 2", you would have to wrap it in square brackets.

ColumnTwo

[Column Two]

[Column2]

[Column 2]

{ } is used to encase a cross sheet reference. In this instance you would use the proper steps for referencing another sheet when building your formula to establish your ranges. The range names will default to "Sheet Name Range #" and go in numerical order depending on how many you have. The range name can be changed to whatever you'd like, but it will always need to be wrapped in curly brackets.

{Sheet Name Range 1}

{Custom Range Name}

When using a SUMIFS function to include multiple sets of range/criteria, the syntax is actually pretty straight forward.

=SUMIFS(range_to_sum, range _1, criteria_1, range_2, criteria_2, range_3, criteria_3, .................)

So following through with our original formula, we would want it to look something like this...

=SUMIFS({Project Costs}, {Market}, Market@row, {Expense}, Expense@row)

Sign in to join the conversation!

Quick Links

Trending discussions

Formula to Convert Scientific Notation to Numerical Value
Hi, I am trying to create a sheet from data that is originally held in .csv files. My goal is to make it so that transferring the data to my sheet should be as simple as just copy and paste. My issue is that two of the fields that I need to copy over are reported in scientific notation (1.3E+004) and I am trying to figure…
Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…