Home

Conditional Formatting Sum Help

%3CLINGO-SUB%20id%3D%22lingo-sub-462592%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Sum%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462592%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20working%20on%20a%20project%20in%20which%20I%20will%20need%20to%20calculate%20the%20sum%20of%20each%20column%2C%20based%20off%20the%20conditional%20format%20color.%20I%20have%20tried%20everything%20to%20using%20the%20conditional%20formatting%20formulas%20as%20just%20a%20regular%20sum%20formula%2C%20as%20well%20as%20even%20looking%20up%20macros%20to%20use.%3C%2FP%3E%3CP%3EHowever%2C%20all%20the%20macros%20I%20found%20were%20for%20the%20whole%20set%20of%20data%2C%20calculating%20the%20sum%20of%20the%20whole%20data%20for%20each%20conditional%20color.%20However%2C%20I%20am%20looking%20to%20calculate%20each%20conditional%20color%20per%20column.%20Any%20help%20would%20be%20greatly%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-462592%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-463337%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Sum%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-463337%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322083%22%20target%3D%22_blank%22%3E%40Jordan93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20conditions%20from%20the%20conditional%20formulas%20can%20be%20used%20almost%20verbatim%20as%20conditions%20in%20a%20Sumproduct%20statement.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%20for%20green%20in%20B1419%20%3DSUMPRODUCT(B%245%3AB%241414*(B%244-A%245%3AA%241414%26lt%3B%3D45))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Efor%20yellow%20in%20B1420%20%3DSUMPRODUCT(B%245%3AB%241414*(B%244-A%245%3AA%241414%26gt%3B45)*(B%244-A%245%3AA%241414%26lt%3B%3D90))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Efor%20orange%20in%20B1421%20%3DSUMPRODUCT(B%245%3AB%241414*(B%244-A%245%3AA%241414%26gt%3B90))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20copy%20across%20the%20other%20columns.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jordan93
Occasional Visitor

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

1 Reply

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. 

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies