Can't get formula to work
All columns are in one sheet
Column A - value
Column B - status - a selection of drop downs
Column C - owners - a selection of drop downs
Hi I'm looking to extract the sum of values in Column A (£s) based on matches of data in column B (a selection of 6 out of 8 status) & C - a single select owner
I have a worksheet set up so the formula in the cell would look up another single worksheet
Help
Best Answer
-
You could use a SUMIFS function. In it's simplest form SUMIFS just requires a range to sum (your column A) and a criteria range (for example column C) and a criterion (for example the owner).
If this was your data
The formula
=SUMIFS(Value:Value, Owners:Owners, "Pluto")
Would give you 846. This is the sum of the two rows owned by Pluto.
You can extend this and add a second criterion range and criteria. For example:
=SUMIFS(Value:Value, Owners:Owners, "Pluto", Status:Status, "Testing")
This would give you 346. Only the row owned by Pluto that has the status of "Testing".
To put this formula on in a separate sheet to the data you replace the ranges with the cross sheet references you set up. The formula would then look something like (reference names will vary):
=SUMIFS({value}, {owners}, "Pluto", {status}, "Testing")
Things are complicated if your drop downs allow multi-select or are contact type. I am going to assume they are not.
If you have 8 possible things in the Status column and want to sum 6 of them, you can add an OR function to the criteria and list the 6 that you want to include like this:
=SUMIFS(Value:Value, Owners:Owners, "Mickey", Status:Status, OR(@cell = "Testing", @cell = "Not started", @cell = "Requirements capture", @cell = "Completed", @cell = "In development", @cell = "Billing"))
In my example, this would return 255,703.
Alternatively, you could write a shorter formula to count rows where the status is not the 2 you don't want.
=SUMIFS(Value:Value, Owners:Owners, "Mickey", Status:Status, AND(@cell <> "Cancelled", @cell <> "On Hold"))
In my example, this would still return 255,703.
Answers
-
You could use a SUMIFS function. In it's simplest form SUMIFS just requires a range to sum (your column A) and a criteria range (for example column C) and a criterion (for example the owner).
If this was your data
The formula
=SUMIFS(Value:Value, Owners:Owners, "Pluto")
Would give you 846. This is the sum of the two rows owned by Pluto.
You can extend this and add a second criterion range and criteria. For example:
=SUMIFS(Value:Value, Owners:Owners, "Pluto", Status:Status, "Testing")
This would give you 346. Only the row owned by Pluto that has the status of "Testing".
To put this formula on in a separate sheet to the data you replace the ranges with the cross sheet references you set up. The formula would then look something like (reference names will vary):
=SUMIFS({value}, {owners}, "Pluto", {status}, "Testing")
Things are complicated if your drop downs allow multi-select or are contact type. I am going to assume they are not.
If you have 8 possible things in the Status column and want to sum 6 of them, you can add an OR function to the criteria and list the 6 that you want to include like this:
=SUMIFS(Value:Value, Owners:Owners, "Mickey", Status:Status, OR(@cell = "Testing", @cell = "Not started", @cell = "Requirements capture", @cell = "Completed", @cell = "In development", @cell = "Billing"))
In my example, this would return 255,703.
Alternatively, you could write a shorter formula to count rows where the status is not the 2 you don't want.
=SUMIFS(Value:Value, Owners:Owners, "Mickey", Status:Status, AND(@cell <> "Cancelled", @cell <> "On Hold"))
In my example, this would still return 255,703.
-
Thank you so much
I used the final shorter formula version
Appreciate you getting me there after days of frustation
-
Great news, glad I could help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!