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

TristaB

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.

Nick Korna

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

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.