LinkBack URL
About LinkBacks
Help!
How to sum values for several days provided that days are separated by blank cells.
a file is attached for explanation.
thank you
Help!
How to sum values for several days provided that days are separated by blank cells.
a file is attached for explanation.
thank you
Why not ...
=(SUMPRODUCT((K$8:K$27>=B8)*(K$8:K$27<=C8)*(L8:L27)))
If you always have an entry for every day you could use something like
Please Login or Register to view this content.
If you want something done right... find a forum and ask an online expert.
Time flies like an arrow. Fruit flies like a banana.
sorry, but the result is not true!
did you try it ?
sorry, but the result is not true!Originally Posted by JohnTopley
did you try it ?
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.
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))
Last edited by JohnTopley; 07-17-2017 at 02:34 AM.
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)
Dave
What is your expected result in this case and why
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
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?
maybe this is more robust
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 -1Please Login or Register to view this content.
Great!maybe this is more robust
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 -1Please Login or Register to view this content.
Appreciation
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks