sumif in up to 4 different series

[karlaane]

Hello, I have a boggle with some calculations that would be nice to solve without a macro.

I have a sheet where there can be up to 4 series of numbers in the same row (one year layout - Jan, Feb, etc. - 365 cells).

I have 4 cells, where I would like to sum the value of series 1, series 2, series 3 and series 4 - IF they exists.

The value will be either blank or numeric.

I was thinking of looking up the first non-blank cell, and calculate until it was blank again, but there is 365 cells to look through, and I don't think I can use that many layers of IF, and how do I then find the second and third series if they exists?

TIA for all help provided.

karlaane

 

[PaddyD]

Hi -welcome to the board.

Could you take us through a detailed, worked example...

 

[karlaane]

[TABLE="class: grid, width: 100%, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[TD="align: center"]P
[/TD]
[TD="align: center"]Q
[/TD]
[TD="align: center"]R
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Serie 1[/TD]
[TD]Serie 2[/TD]
[TD]Serie 3[/TD]
[TD]serie 4[/TD]
[TD]01.01.13[/TD]
[TD]02.01.13[/TD]
[TD]03.01.13[/TD]
[TD]04.01.13[/TD]
[TD]05.01.13[/TD]
[TD]06.01.13[/TD]
[TD]07.01.13[/TD]
[TD]08.01.13[/TD]
[TD]09.01.13[/TD]
[TD]10.01.13[/TD]
[TD]etc..[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]4 (sum of H7:J7)[/TD]
[TD]2 (sum of L7:M7)[/TD]
[TD]10 (sum of O7:P7)[/TD]
[TD](no more series)[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]6 (sum of J8:K8)[/TD]
[TD]9 sum of M8:O8)[/TD]
[TD](no more series)[/TD]
[TD](no more series)[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






To sum cells to the right (in "calendar - 356 cells") if they have a value, and in series as shown. There might be only one serie, there might be up to 4 series. but there will be one or more blank cells between them.

Hope this was more understandable ?

TIA

karlaane

 

[PaddyD]

Are the following always true:

- there are a maximum of four series
- there will be at least one blank cell between the end of one series and the start of another

?

Also - condensing the calcs into a single step can be done, but the resulting formula will rather opaque. Do you care if we 'show our workings' by using extra cells for intermediary calculations?

 

[karlaane]

Are the following always true:

- there are a maximum of four series - Yes.
- there will be at least one blank cell between the end of one series and the start of another - Yes, one or more.

?

Also - condensing the calcs into a single step can be done, but the resulting formula will rather opaque. Do you care if we 'show our workings' by using extra cells for intermediary calculations? -No, I can insert a "support/calc" column if needed. Do you need one pr. series?

karlaane

TIA
karlaane

 

[karlaane]

In case my post inside-quote has not been read:

-Yes, there will be a maximum of four series.
-Yes, there will always be at least one blank cell between series.
-No, I do not care if you need a calc/holding-cell.

TIA.
karlaane

 

[PaddyD]

Hi.

First step - set up a table that returns the start and end points for the series. We'll use your first row of data as an example - I had it in f66. We're going to have a 5 column table that holds position of start and end of each series.

1) identify start of first series:
=MIN(IF(LEN($F3:$P3)>0,COLUMN($F3:$P3)))

...entered with control + shift + enter, not just enter. I entered this in d7

2) Identify gaps between series:
=MIN(IF(LEN($F3:$P3)=0,IF(COLUMN($F3:$P3)>D7,COLUMN($F3:$P3))))

...entered with control + shift + enter, not just enter. I entered this in e7, and copied across to h7. Copy all formulas down for as many rows as you need.

Second step - use these column numbers to define ranges that you feed into a sum:

in i7:
=SUM(INDEX(3:3,,D7):INDEX(3:3,E7))

...and copy across to l7.

 

[karlaane]

Hi PaddyD.

Thankyou for your reply, but there is a question ...

Could you plz explain a bit further? I do not understand how you refer to a table in F6:P6, and formulas calculate with F3:P3, in row 7...? Is there something I am missing out on, or is it a typo?

Could you plz draw up a table or similar, as the one I used in the post of 27.Feb?

TIA
karlaane