Forum Discussion

DC. Young's avatar
DC. Young
Copper Contributor
Mar 31, 2018

[solved] Formula Sum of Values under Matched Content/Heading Condition

I'm trying to create a formula that sums the values under one heading if the row the value is in also has a specific content under another heading. For example (see attached .png), a sum of all the values under Total2 (Heading) whose row also contains "UB" under Loop (Heading) (resulting with the sum of 107, and adjust to changes in values/Loop content; ie: If all 'TD S' became "UB", the sum would change from 107 to 315).

 

Current formula: =SUMIF(Table7[[#All],[Loop]], "UB",Table7[[#All],[Total2]])

- Extended thanks to Sergei Baklan

Formulas & Functions
Need Help
    • DC. Young's avatar
      DC. Young
      Copper Contributor
      Mar 31, 2018

      !!!
      Adjusted.
      =SUMIF(Table7[[All],[Loop]],"UB",Table7[[#All],[Total2]]) worked!

      Thank-you!

       

      The issue now is the formula's handling of negatives; where the current sum of UB=115, if the UB with the value of 8 becomes -8, the sum becomes 99 instead of 107 (115-8).

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Apr 16, 2018

        Missed that...

        1) You don't need [#All] here. That adds to calculations header and total of the column - not necessary.

        2) IMHO, the result is correct. If all positive UB:s are 115 in sum, other words =107+8 with changing plus eight on minus eight the sum will be =107-8 or 99

Resources