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

Andrea Prochaska

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.

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

     B

     C

.

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)