sum cells formatted strikethrough

[Bossie]

Hi,

I want to sum a range (say a5:a20). But some values have been 'striked through' (done manually)
Is there a (non vba) way to sum all the values, except the 'striked through' cells?

2000
4000
6000
8000
sum= 14000

 

[NARAYANK991]

Hi ,

This is probably the only way ; use the Excel 4.0 macro functions to retrieve the cell formatting.

See the attached file. Since these are macro functions , the file needs to be saved as a .xlsm file.

Narayan

 

[Bossie]

thx. That is great!

 

[bosco_yip]

Another option without helper cells

1. Define Name >>

>> Name : SumCellsExceptStrikethrough

>> Refer to :

=SUM((1-GET.CELL(23,IF(1,+OFFSET($A$2:$A$100,ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100)),))))*$A$2:$A$100)

2] In C2, enter :

=SumCellsExceptStrikethrough

p.s. Since Get.Cell is a Macro 4 function, the file should be saved in Excel .xlsm type

Regards
Bosco

 

[Bossie]

thx, Bosco
But, would you like to explain what this does? I mean, I copied it and it works, but I don't understand why...
The bit after "if(" is difficult to understand...

grtz
Hugo

 

[bosco_yip]

thx, Bosco
But, would you like to explain what this does? I mean, I copied it and it works, but I don't understand why...
The bit after "if(" is difficult to understand...

grtz
Hugo

Hi,

The IF function returns "cell reference" type array outputs.

Further details, please refer to this link post #.3 explanation by Lori

https://chandoo.org/forum/threads/something-informative.25700/#post-155252

Regards
Bosco