Formula for adding percentages of a single resource with multiple tasks.

Hello,

I am trying to get the sum of several percentages within a range that meet certain criteria. I have tried to make the formula on my own and by using the AI Formula generator to help but I keep getting errors with the results (either #DIVIDE BY ZERO or #INVALIDE OPERATION)

Here is my example:

So I want to add the total for the 4 percentages in "Resource 1 Allocation" column for the duplicated resource in Resource 1 column and have it display on all 4 lines of the Column46. My sheet is currently filtered, so the sum would need to include a criterion of "High" in the Resource 1 Alert column. So, I would want 675% to show in Column46 for this resource.

I am thinking It may be an issue with the percentages as numbers, etc, as I have seen some posts with that error in the past. However, in general I want to know how I can add the sum of multiple rows that meet certain criteria.

Find more posts tagged with

Accepted answers

Nick Korna

Check your data columns - if there are errors in these, it will cause an issue like this.

All comments

Nick Korna

Hi @TristaB,

Something like this should work for you:

=SUMIFS([Resource 1 Allocation]:[Resource 1 Allocation], [Resource 1 Alert]:[Resource 1 Alert], "High", [Resource 1]:[Resource 1], [Resource 1]@row)

Sample (using the data from your example):

If you're still getting errors, or you have any problems/questions then just let us know!

TristaB

Hi @Nick Korna

With that formula, I get the #DIVIDE BY ZERO ERROR.

Nick Korna

Check your data columns - if there are errors in these, it will cause an issue like this.

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.