Forum Discussion

Jordan93's avatar
Jordan93
Copper Contributor
Apr 16, 2019

Conditional Formatting Sum Help

Hello!

 

I am currently working on a project in which I will need to calculate the sum of each column, based off the conditional format color. I have tried everything to using the conditional formatting formulas as just a regular sum formula, as well as even looking up macros to use.

However, all the macros I found were for the whole set of data, calculating the sum of the whole data for each conditional color. However, I am looking to calculate each conditional color per column. Any help would be greatly appreciated

excel

1 Reply

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Apr 16, 2019

    Hello Jordan93 

     

    The conditions from the conditional formulas can be used almost verbatim as conditions in a Sumproduct statement. 

     

    Try this for green in B1419 =SUMPRODUCT(B$5:B$1414*(B$4-A$5:A$1414<=45))

     

    for yellow in B1420 =SUMPRODUCT(B$5:B$1414*(B$4-A$5:A$1414>45)*(B$4-A$5:A$1414<=90))

     

    for orange in B1421 =SUMPRODUCT(B$5:B$1414*(B$4-A$5:A$1414>90))

     

    Then copy across the other columns. 

     

Resources