Sum only Cells where cell is filled red based on conditional formatting

[kac1125]

7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023
8	15	25	35	6

Lets say that 8/1,9/1,10/1 are filled red because the number is greater than 10. How Can I sum up just these values not including the 7/1 and the 11/1.
Thank you in advance for your help!

 

[Joe4]

Let's say that your range above is A1:E2.
Then just use this formula:

=SUMIFS(A2:E2,A2:E2,">10")

 

[kac1125]

Let's say that your range above is A1:E2.
Then just use this formula:

=SUMIFS(A2:E2,A2:E2,">10")

Thank you for your response, But what i am really looking for is to sum up anything that is highlighted based of a conditional format. The example I gave was just to show the cells that were highlighted based on the conditional formatting. The issue is the condition may change on other cells in in the spreadsheet. If that make sense? Thanks again appreciate your response.

 

[Joe4]

Excel formulas cannot run off of formatting/highlighting. They can only run off of values.
But the lesson to be learned here is that whatever "rule" you are using for your Conditional Formatting you can usually use in a SUMIFS formula, like I showed. Just use the same condition.

If you have something more complex that you cannot do that or if you manually highlighted the cells, you would have to use VBA to do that you want.
If you search this forum or do a Google search on "Excel Sum By Color", you will find hundreds of posts & solutions on it.

 

[kac1125]

Excel formulas cannot run off of formatting/highlighting. They can only run off of values.
But the lesson to be learned here is that whatever "rule" you are using for your Conditional Formatting you can usually use in a SUMIFS formula, like I showed. Just use the same condition.

If you have something more complex that you cannot do that or if you manually highlighted the cells, you would have to use VBA to do that you want.
If you search this forum or do a Google search on "Excel Sum By Color", you will find hundreds of posts & solutions on it.

Thank you!

 

[kac1125]

Excel formulas cannot run off of formatting/highlighting. They can only run off of values.
But the lesson to be learned here is that whatever "rule" you are using for your Conditional Formatting you can usually use in a SUMIFS formula, like I showed. Just use the same condition.

If you have something more complex that you cannot do that or if you manually highlighted the cells, you would have to use VBA to do that you want.
If you search this forum or do a Google search on "Excel Sum By Color", you will find hundreds of posts & solutions on it.

Hello again, the condition I am using on the conditional formatting is =AND(D$1<=$C2,D$1>=$B2). would the formula be =SUMIFS(A2:E2,A2:E2,"=AND(D$1<=$C2,D$1>=$B2").
Thanks!

 

[Joe4]

No, each Condition in your AND gets its own condition in your SUMIFS function (you do not put AND in the SUMIFS).
See here for details: SUMIFS function - Microsoft Support

If you still need help, please post an example along with your expected results. Be sure to include all the data referenced by your formulas, and be sure to include the row and column headers so we can see exactly what sections your formula is referring to.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.