Formula help with adding up total $ in one column and associating it to the right business unit

Kristina M

I am having trouble coming up with the right formula for a sheet. I am referencing a sheet to create a formula in another sheet. In the sheet I am creating the formula in, I am trying to calculate (from the reference sheet) the cost by business unit. Here are the two columns I am trying to create the formula for:

I need the formula to total up the cost and associate the total cost to the correct business unit. I am trying to pull those totals into this sheet:

Any help would be much appreciated! Thanks!

Jennifer Kurtz

Hi @Kristina M ,

You could use a SUMIF formula here, I think. You'd put the formula in your "Cost by BU" sheet. You'd reference the other sheet for your formula ranges, and your criterion would be your "Cost by BU" column. (I don't know what that column is actually titled, but that's how I'll reference it here. :> ) Here's an example:

= SUMIF({Business Unit from first sheet}, [Cost by BU]@row, {Costs from first sheet}).

The formula will look for applicable Business Unit and total up the costs for that unit.

Does that help?

Jennifer Kurtz

@Kristina M - Hmmm… My first suggestion would be to check the ranges you are referencing on the other sheet to make sure they're set appropriately. (Sometimes, you'll select a column for the range and it "resets" itself to only the top left cell; kind of a weird refresh sort of thing…)

The formula should sum the entries from your first sheet when they match exactly to what you have entered in your Description column on the second sheet (where the formula is).

If the ranges are all good, my second thought is to try it in another row. You've tried it in your "All" row, and you have an entry on the first sheet for "All," - so I thought that would pull in the right $$ value. But perhaps that's a calculated field on your first sheet and the way it's calculated is causing a hiccup? (If it works in the other rows, you could simply total those for your "All" row.)

Jennifer Kurtz

Hi @Kristina M ,

You could use a SUMIF formula here, I think. You'd put the formula in your "Cost by BU" sheet. You'd reference the other sheet for your formula ranges, and your criterion would be your "Cost by BU" column. (I don't know what that column is actually titled, but that's how I'll reference it here. :> ) Here's an example:

= SUMIF({Business Unit from first sheet}, [Cost by BU]@row, {Costs from first sheet}).

The formula will look for applicable Business Unit and total up the costs for that unit.

Does that help?

Kristina M

@Jennifer Kurtz Thank you so much for the quick reply! Here's a full look into this sheet with column names - I should have given that from the start, apologies! I tried using:

=SUMIF({Business Unit}, [Description]@row, {Costs})

But I am getting an "UNPARSEABLE" error. Any thoughts?

Jennifer Kurtz

@Kristina M - Hmmm… My first suggestion would be to check the ranges you are referencing on the other sheet to make sure they're set appropriately. (Sometimes, you'll select a column for the range and it "resets" itself to only the top left cell; kind of a weird refresh sort of thing…)

The formula should sum the entries from your first sheet when they match exactly to what you have entered in your Description column on the second sheet (where the formula is).

If the ranges are all good, my second thought is to try it in another row. You've tried it in your "All" row, and you have an entry on the first sheet for "All," - so I thought that would pull in the right $$ value. But perhaps that's a calculated field on your first sheet and the way it's calculated is causing a hiccup? (If it works in the other rows, you could simply total those for your "All" row.)

Kristina M

@Jennifer Kurtz Actually it was user error - face palm - It works! Good to go! I really appreciate your help with this!!

Jennifer Kurtz

Happy to help and glad you got it working! :D

Have a great Monday!

Kristina M

@Jennifer Kurtz Thank you! You too!