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

Find more posts tagged with

Accepted answers

KPH

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.

All comments

KPH

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.

Gordon SSAmbassador

Thank you so much

I used the final shorter formula version

Appreciate you getting me there after days of frustation

KPH

Great news, glad I could help.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.