How to total values for days separated by empty cells

[nooredein]

Help!
How to sum values for several days provided that days are separated by blank cells.

a file is attached for explanation.

thank you

[JohnTopley]

Why not ...

=(SUMPRODUCT((K$8:K$27>=B8)*(K$8:K$27<=C8)*(L8:L27)))

[scottiex]

If you always have an entry for every day you could use something like

Please Login or Register  to view this content.

[nooredein]

sorry, but the result is not true!
did you try it ?

[nooredein]

Why not ...

=(SUMPRODUCT((K$8:K$27>=B8)*(K$8:K$27<=C8)*(L8:L27)))

sorry, but the result is not true!
did you try it ?

[scottiex]

I'd prefer to fix the problem at the source and make sure the data always has the date next to it that corresponds to it.

Maybe it is created from a pivot table where you can tell it to fill all the headers/dates in or maybe you just need a column next to it that goes "if there is a date then take that date if not use the cell above" - and just fill it down then you can use all the normal formulas pivot tables and so forth on your data.

[JohnTopley]

Sorry .. misunderstood (dumb me!): need SUM with OFFSET or INDEX.


=SUM(INDEX(L8:L27,MATCH(B8,K8:K27,0)):INDEX(L8:L27,MATCH(C8,K8:K27,0)))

OR

=SUM(OFFSET($K$7,MATCH(B8,K8:K27,0),1,MATCH(C8,K8:K27,0)-MATCH(B8,K8:K27,0)+1))

[FlameRetired]

Maybe:

=SUMPRODUCT(FREQUENCY($K$8:$K$27,$H$8:$H$27),--($H$8:$H$28>=$B$8),--($H$8:$H$28<=$C$8),$L$8:$L$28)

[samba_ravi]

What is your expected result in this case and why

[nooredein]

Sorry .. misunderstood (dumb me!): need SUM with OFFSET or INDEX.


=SUM(INDEX(L8:L27,MATCH(B8,K8:K27,0)):INDEX(L8:L27,MATCH(C8,K8:K27,0)))

OR

=SUM(OFFSET($K$7,MATCH(B8,K8:K27,0),1,MATCH(C8,K8:K27,0)-MATCH(B8,K8:K27,0)+1))

Both are working. Big thanks
BUT! when you choose the period From- To it did't include all the values of the TO date, it sums up to the first value of the TO date.
How can we fix this?

[scottiex]

maybe this is more robust

Please Login or Register  to view this content.

of course change the 27's to be 1000 or whatever if your list gets longer the 8 is the start row the 7 is the start row -1

[nooredein]

maybe this is more robust

Please Login or Register  to view this content.

of course change the 27's to be 1000 or whatever if your list gets longer the 8 is the start row the 7 is the start row -1

Great!
Appreciation