Ask Your Question

How to include/exclude cell values in a Sum formula? [closed]

asked 2014-10-22 01:03:24 +0200

heartcore gravatar image

I want to have an option like a checkbox or something to include exclude the values of each cell from a sum function. Is that possible?


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 09:40:45.427300

2 Answers

Sort by » oldest newest most voted

answered 2014-10-22 12:20:11 +0200

JohnSUN gravatar image

Yes, it's possible SumByMarked.ods

edit flag offensive delete link more



@JohnSUN: You are a great programmer as we know and you rule over the tricky fringes of number formats. But "heartcore" will hopefully not need a SUB to accomplish his task. The cell having entered a "switching character" like "x" should be enough of "checkbox or something".

Lupp gravatar imageLupp ( 2014-10-22 14:04:52 +0200 )edit

I have always believed that the tool should be easy to work. It is much easier to mark the required position by click rather than enter anything from the keyboard. And if it is possible, why not implement it? Unfortunately, I don't know an easier way to insert a checkbox in a cell. (Thanks for a " great programmer" :-) )

JohnSUN gravatar imageJohnSUN ( 2014-10-22 15:50:20 +0200 )edit

@JohnSUN - This is a great solution! But how did you create the little squares? The black and white one are both default style in the conditional formatting?

ROSt52 gravatar imageROSt52 ( 2014-10-22 16:06:45 +0200 )edit

@ROSt52: I'm confident you'll find out about the little squares youself. Take it as an amazing puzzle. @JohnSUN: I really appreciate what you demonstrated, and as I also read the other forum you contribute to, my "great programmer" was an honest approval.

Nonetheless I would prefer doing it without your SUB, even having to rely on the keyboard. What about delivering / portability? If I get offered an odf document containing a "macro" I always hesitate. I wouldn't dissuade others to do so.

Lupp gravatar imageLupp ( 2014-10-22 16:17:39 +0200 )edit

I just picked Insert - Special Character and scroll down until see icons U+25A0 and U+25A1. (In general I was looking for images of checkboxes "on" and "off"). Then I create style MarkedCell with number format "■";#,##0;"□";@ (positive - black box, negative - digits, zero - white box, text - as is). It isn't conditional formatting...

JohnSUN gravatar imageJohnSUN ( 2014-10-22 16:26:35 +0200 )edit

@Lupp It was assumed that the macro will be moved from the workbook to My Macros, and will work for any workbook in which we want to enable or disable checkboxes. (By the way, the handler of event "DblClick" NOT SUB! It's FUNCTION! Return True to cancel Edit!)

JohnSUN gravatar imageJohnSUN ( 2014-10-22 16:35:50 +0200 )edit

@JohnSUN: You surely know much more about programming in specific when it comes to StarBasic. In my terms functions are a subset of subroutines and I used "SUB" just as an abbreviation. But that's not of much meaning. I respect your opinion, of course. Nonetheless I will now go to answer the question myself attaching my much less subtle solution. I hope it will be understandable on a medium level and it will be easily adaptable to solve other similar tasks.

Lupp gravatar imageLupp ( 2014-10-22 18:26:01 +0200 )edit

@Lupp OK, If you really sure that thirteen lines of macro is not clear for medium level then let it be without macro SumByMarkedWithoutMacro.ods

JohnSUN gravatar imageJohnSUN ( 2014-10-23 13:06:38 +0200 )edit

@JohnSUN - thanks for the information on how you made the solution; it still is a kind of an amazing puzzle (as @Lupp suggested). I save your sample file for the future.

ROSt52 gravatar imageROSt52 ( 2014-10-23 16:52:39 +0200 )edit

answered 2014-10-22 18:34:46 +0200

Lupp gravatar image

updated 2014-10-22 18:35:59 +0200

As advertised in the above comment I attach an example. It shall demonstrate a rather simple solution without programming ("macro") on the one hand. On the other hand it shoud show how manifold possible adaptions to varying needs might be on an unchanged basis. I should repeat that I fully recognise the solution provided by JohnSUN. ask41449SelectElementsForSum001.ods

edit flag offensive delete link more

Question Tools



Asked: 2014-10-22 01:03:24 +0200

Seen: 1,240 times

Last updated: Oct 22 '14