Hi,
i have a range of cells (numbers) that have conditional formatting applied based on the value of another cell. how can i sum up the cells that are not conditionally formatted to strike-through?
Thanks
Hi,
i have a range of cells (numbers) that have conditional formatting applied based on the value of another cell. how can i sum up the cells that are not conditionally formatted to strike-through?
Thanks
You would need to incorporate the same test done by CF into a formula.
Thanks for getting back to me on this Andy; but i would actually need your help on this one. Can you guide me through?
Start by posting an example of your workbook.
OK; the sheet is attached; the figures in the calendar follow a conditional format under column A; if the order is cancelled; then the quantity is struck-out (i would like to keep the number rather than just deleting or hiding it) - so i want the totals to be summed without the figures that have been cancelled.
Thanks a lot
BL4: =IF($A4="Cancelled",0,P4+T4+X4+AB4+AF4+AJ4+AN4+AR4+AV4+AZ4+BD4+BH4)
Note your conditional formatting only extends over O4:AT50 is this intentional?
thanks a lot; unfortunately the sum turns zero i can't make it work
(the formatting is made like this because i only received cancellation confirmations up to Aug-10)
Halak ,
If you only need to strikethrough the number (total) you set you conditional fomat with the strikethrough format.
Select your cell BL4
Menu : Format - conditional formatting
Select in condition1 = Formula is instead of Cellvalue is
Type in the condition : =$A4="Cancelled"
Click on the Format... button and select the "effect" Strikethrough.
Click OK (twice) to close the conditional format form.
Now your data will be formatted.
So you need to adjust the formula to test only those cells included in the CF test.
=IF($A4="Cancelled",0,P4+T4+X4+AB4+AF4+AJ4+AN4+AR4)+AV4+AZ4+BD4+BH4
Of course this is not very dynamic.
If you add a row to the top of your data and place a 1/0 in those that have CF applied you can use a SUMPRODUCT formula.
=IF($A5="Cancelled",SUMPRODUCT(($O3:$BJ3="Forecast")*($O$1:$BJ$1=1)*$O5:$BJ5),SUMPRODUCT(($O$3:$BJ$3="Forecast")*$O5:$BJ5))
Thanks a million Andy! Works fine! Don't know why it wasn't calculating earlier.
Cheers
Hey guys,
Pretty much an Excel novice so all the help is very much appreciated. I'm trying to do what I thought would be a checked off and struckthrough "simple" task list. Have a check box that when checked shows as True or False in another cell. Then I have an IF function that I'm trying to use to strikethrough text in another cell when the checkbox is filled.
Example:
Column A
Name of person
Column B
Reason for absence
Column C
Checkbox
Column D
True or False
When the checkbox in Column C (named Visited) is checked > it registers as True > then I am trying to use the following formula =IF(D1=TRUE,"sick","sick") to change the text to strikethrough based on conditional formatting tip you showed above. However it's not striking through the text.
Pretty much trying to use this to checkoff and strikethrough text on items in the spreadsheet as people finish those tasks.
Example of how I'd like it to work:
(Before visit is made)
Column A
Mr. Jones
Column B
Nursing Home
Column C
checkbox named "Visited"
Column D
True or False (based on checkbox)
After visit is made
Column A
Mr. Jones(text with strikethrough)
Column B
Nursing Home (text with strikethough)
Column C
checked
Column D
True
Hope that makes sense, and thanks for any help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks