Sum individual cells (not array) if not #N/B

[Lucca]

Hi there,

In cell E3, I would like to sum 21 cells Q3;AC3;AO3;…;IW3. (Note that those cells differ each time 12 columns). The easy formula =sum(Q3;AC3;AO3;…;IW3) results in #N/B. This is because in that cells there are #n/B’s resulting from inner formulas.
I could change all formulas of these cells with IF.NB(…;"") but this is not very efficient…

So, could anyone think about a good formula. This formula is to be copied to E4 until E22, so necessary absolution and dynamic into the formula is needed.

Thanks a lot
Lucca

[PKG]

@Lucca ,
Only if you upload the Calc file!

Please remove confidential data beforehand!

[Villeroy]

=SUM(Q3:IW3) but you need to replace the existing formula with =IFNA(existing formula;0). In your formula language, IFNB might be the right name.

[sokol92]

@Villeroy

but you need to replace the existing formula

or without changing the formulas

=SUMPRODUCT(IFERROR(Q3:IW3;0) )

[Lupp]

I’m affraid the questioner has a sheet with data for many years using one column for every month of each year.
Now het may want to add the (say) turnovers for the aprils of all those years.
Of course, thats a bad design as often pointed out in this site, and should be reconsidered.
However it’s also a challenge to get results of the kind by ordinary formulas - which, of course will fail if somewhere a column is inserted or deleted for special reasons. Even based on the supposed design there are better solutions with 12 (e.g.) helper rows.
See:
diasak111592StrangeAddingForPeriodicNoncontiguousCells.ods (16.3 KB)

[Lucca]

asklibreofficesumcells.ods (18.8 KB)
Hi,

Thanks for your answers and suggestions, fellows,
some replies to yours.
@Villeroy @sokol92
SUM(Q3:IW3). Its not summation of the total array that is been looking after. It is only the cells Q3 + AC3 + AO3 and so on.
@Lupp
The workbook is about following riders in a big tour like e.g. Giro di Italia. The total screening of the tour consists of 4 workbooks and you are right, I could have made a better plan before the start of my project. I however started without knowing where to end and therefore now I have plenty of references in the formulas that give me NA not available for some references refer to empty cells (for instance stages of the tour that still need to be riden.

Thanks for looking at the imbedded spreadsheet. Note that I’ve replaced the formulas by its values for when I would not have done this, I would have needed to send all 3 other workbooks aswell (way to big documents).
Thanks.

[PKG]

@Lucca ,
Form the totals with the aggregate function.

AGGREGATE function

Unbenannt 1.ods (13,7 KB)

[Lucca]

Thank you, PKG.

Never heard of this function. Diving in it I understand it this way:

AGGREGATE( index of function to fulfill with your 9 here being SUM ?
index in options to do so, your 6 must be to neglect the NA’s (or N/B’s in dutch)

so far so good,

only next doesnt give me the result looked for. this function seems to sum all numbers in the array that are not NA (N/B). The cel Q3 for instance is already the sum of I3:P3. I eventually could divide the aggregate()/2, but that doesn’t work because as there are also numbers in for instance G and H 3 (numbers that have only a conditional meaning (G 1= in the stage/0 = dropout of the tour ; H if , 1 this is the headman assigned) the array in your aggregate() is not suited.

Maybe, maybe

I could do the following:
=aggregate(9;6;Q3:IW3)/2 - sum(G3;H3,R3;S3,AD3,AE3,…)

Though quite time intensive I will try and let you know whether that does give me the expected result.

By the way, I will look up where to find the indexes of function and option.

[mikekaganski]

AGGREGATE allows to ignore cells with other AGGREGATE

[keme1]

Attached is an edited version of your sheet, with a suggested SUMPRODUCT(IFERROR()) approach, akin to @sokol92’s comment.
asklibreofficesumcells.ods (18.7 KB)
This does not use the “cycle of 12” to identify which cells to sum, but rather it depends on the column heading. If you change any other headings to start with “Int voor…”, it will break the formula (unless those columns should also be counted, of course, in which case all should be well).

[Lucca]

Thanks Keme1. This does it in an efficient way. Thank you very much.

[keme1]

Note also that while in LO Calc boolean TRUE and numeric 1/nonzero values are interchangeable (and FALSE/zero also) , MS Excel (and possibly other spreadsheet apps) distinguish between numeric and boolean. If you need compatibility with other platforms, you may need a conversion step in the formula.

I have not tested it in Excel. For all I know, it may also work as intended.

[Lucca]

thank you Keme1