LinkBack URL
About LinkBacks
Hi,
I need a SUM of column that will ignore value that is strikethrough. Is that possible?
Hi,
I need a SUM of column that will ignore value that is strikethrough. Is that possible?
My English is very poor, so please be patient >_<"
Thanks & Regards.
hkbhansali
You'd need to use VBA to do that, either create a UDF or a macro. Would that be okay?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
short answer - no, not with formulas. strike-through is formatting/cosmetic, not data, formulas work with data. How is the strike-through being applied?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi chemistB
VBA or macro anything okay with me.
Thanks
e.g.
Please Login or Register to view this content.
=MySUM(A2:A50)
Shouldn't you make that volatile?
Application.Volatile
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi john Topley
how to do this ?
Even if it's volatile, it won't be responsive to formatting changes.
Entia non sunt multiplicanda sine necessitate
Yes, I know.
But at least it will update upon the next calculation.
@Tony, Of course ..!
Last edited by JohnTopley; 02-09-2016 at 01:14 PM.
This is a perfect example of why calculations should not be based on formats!
Amen, but there's no persuading -- people have to get bit to believe it.
@Tony .... but it is imperfect world ....
So is that a given (except for instances which I can't even think of)
"Whenever creating a UDF, make sure it is volatile." ?
No -- most UDFs should not be volatile, for the same reason we avoid volatile functions, like INDIRECT and OFFSET.
ALT + F11 will open VBA Editor.How to do this?
Then Insert > Module
Paste this code into the large white text box
Please Login or Register to view this content.
Yes,
It's work....but it's possible that auto update this code ? (My sum is in column "E")
Thanks a lot ChemistB
Last edited by hkbhansali; 02-13-2016 at 09:38 AM.
Put the function in any cell
=MySum(E2:e100)
just as you would for =SUM(E1:E100)
I'm assuming you've discovered that the formula doesn't update when you add/remove the strike-through to a cell?Originally Posted by hkbhansali
That's the reason you should not base calculations on cell formats.
When you add/change a cell format that action does not trigger a calculation so any calculations based on that format will not update.
In the thread we mentioned making the UDF volatile so that the formula would update when some event would trigger the NEXT sheet calculation.
I think you'd be better off using some "marker" to define which cells should be summed rather than using the strike-through format which is not reliable.
Something like this:
Data Range
Sum the values marked by "X"...
=SUMIF(B1:B10,"X",A1:A10)
@tony: Couldn't agree more!
Tony Valco
This was awesome!!! Thank you so much!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks