Hi
I have attached one file containing 2 sheets
in second sheet I am trying to calculate date Range wise Sum
but date wise values are in first sheet
#Value! error is coming
Atul
Hi
I have attached one file containing 2 sheets
in second sheet I am trying to calculate date Range wise Sum
but date wise values are in first sheet
#Value! error is coming
Atul
Here is one possible solution for you. paste into Sheet B G10
Formula:Please Login or Register to view this content.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Try this..
This will also...
Formula:Please Login or Register to view this content.
Don't forget to click *
Last edited by JBeaucaire; 08-29-2014 at 01:11 PM.
Thanks for Reply @ Glenn Kennedy and Vikas_gautam
I think there is confussion for understanding Query
I have given date Range From Date in G8 and To date in H8
when I am changing from date then ouput is not coming correct
because in your formula you have not given reference for cell G8
Atul
I assumed that you always wanted to start from the first of the month. Leave it with me...
OK. Sorted out (I hope). Try this.
Try this.. in G10 Sheet B
Formula:Please Login or Register to view this content.
Don't forget to click *, again
Last edited by JBeaucaire; 08-29-2014 at 01:10 PM.
Yes Now Value is comming correct
Thanks to Vikas and Glenn
Dear Glenn why you took Reference of Column no. 5 that I didnt understood
Atul
Your data begins in column F - 6 as a number. The match formula returns a 1 for the 1st August in column F. If you add 1 to 6 it would start the sum range 1 column to the right. So, I subtracted 1. To be honest, Vikas' solution is simpler. I'd use it, if I were you.
Exact
I am using Vikas's Solution only because it is more easy and simple
But I want to know why you took reference of Column no. thats why I asked you.
I am happy that you declared that Vikas's solution is perfect
Thanks Vikas
Hi Atul..
Here is the explanation...
Formula:Please Login or Register to view this content.
=Offset(Base_Reference, Row_Offset, Column__________Offset, Height , Width )
=Offset( A!F7 , 1 , MATCH(G8,A!F7:AJ7,0)-1 , 7 , MATCH(H8,A!F7:AJ7,0))
As you can see above, I am using Column Offset to set your start date and Width to set your end date...
I have deducted 1 because Column offset is exclusive of base reference and width is inclusive of Base Reference...
Use formula auditing to understand it more..
and use excel inbuilt help to know more about the offset function..
Don't forget to click *
Last edited by JBeaucaire; 08-29-2014 at 12:22 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks