Formulas to apply conditional formatting and summing based on checkmark

[BeverlyG]

I followed the advice given by Dave Peterson (in /excel-general/483927-can-you-sort-with-check-boxes) to use checkmarks rather than control boxes to allow sorting. Now I want to:

1. Apply conditional formatting to highlight rows that don't have a checkmark
2. Sum a column of numbers based on whether there is a checkmark in a different column

To summarize, I want to sort by a date column when dates change, but I want to highlight all rows that don't have a checkmark and when a checkmark is entered for a row (i.e. when it is completed) remove the highlighting.

[JeteMc]

It may help one of the contributors to provide a solution if you could utilize the instructions in the banner at the top of the page to upload a sample that illustrates the processes you wish to accomplish.
Please be sure to manually highlight rows and sum columns in the sample so that we will know what our formulas/code should accomplish.
Let us know if you have any questions.

[BeverlyG]

I am attaching a sample spreadsheet. I use it to track when bill are due and paid each month. Since the due dates vary from month to month I want to be able to sort by date, shich is what makes using the control checkboxes difficult. However, I do like to remove the highlighting when paid.

[JeteMc]

I suggest the following:
1. Set formatting to general
2. Set font to Wingdings 2
3. Use shift + p for check mark
The conditional formatting formula is: =$G5<>"P"
The formula to sum unchecked amounts is: =SUMIFS(I5:I23,G5:G23,"<>P")
Let us know if you have any questions.

[BeverlyG]

Thank you. The formulas worked great, once I copy and pasted them into the file. For some reason, the conditional formatting formula wouldn't work when I manually entered it. Thank you so much for your help.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.