We have two sheet one is data & secode is result.
In data sheet :
column A is : Invoice date
column B is : order date
column C is : amount
*************************
I required in result sheet :
column A is : month
column B is : amount
Now we required formula in result sheet in B2 between two date & also invoice date & order date between april-09 and in column B3 between may-09.
Attach file for your ready ref.
Last edited by avk; 03-10-2010 at 01:24 PM.
Hi,
See attached sample ...
HTH
If we have change year in data sheet suppose all apr-09 & may-09 changed with apr-08 & may-08 respectively after that in result sheet total not showing zero.
Please note that, in that sheet number of records & index in receipt number only. In that sheet we don't want to sorting or indexing because our other data may be mismatch. We required all a1 to a65000 and b1 to b65000 in those column record of apr-08, apr-09, may-08, may-09 & so on upto mar-08 & mar-09.
Hi,
You can adjust the ranges to fit your requirements with following
HTH=SUMPRODUCT((MONTH(data!$A$2:$A$113)=MONTH(A2))*(MONTH(data!$B$2:$B$113)=MONTH(A2))*(YEAR(data!$A$2: $A$113)=YEAR(A2))*(YEAR(data!$B$2:$B$113)=YEAR(A2))*(data!$C$2:$C$113))
..
SUMPRODUCT((MONTH(GRR!$G$2:$G$65000)=MONTH(A2))*(MONTH(GRR!$I$2:$I$65000)=MONTH(A2))*(YEAR(GRR!$G$2: $G$65000)=YEAR(A2))*(YEAR(GRR!$I$2:$I$65000)=YEAR(A2))*(GRR!$AB$2:$AB$65000))
..
After input your above formula amount show correctly. If inv date & oa date both between april-09.
But if oa date between april-09 & inv date after april-09 i.e. may-09, june-09, july-09
in that case amount not calculate.
I explain in detail:
If oa date between 01-april-09 to 30-apr-09 & if total amount is : 100/-
1] If oa date & Inv date between 01-april-09 to 30-apr-09 & total amount : 70/-
2] If oa date between 01-april-09 to 30-apr-09 & inv date after 30-apr-09 & total amount is : 30/-
As per your suggested formula amount show : 70/- but amount 30/- not show. In that case can be suggest you.
I attach file with example for more clarity.
bump no reply. Awaiting reply.
I woudl first make the point that using Arrays / SUMPRODUCTs over large ranges is generally going to kill the performance of your file - these functions are "expensive".
On that basis - to try and limit the damage as much as possible I would first suggest creating a Named Range, eg:
with the above setup - and assuming I've understood:Name: _Data RefersTo: =data!$A$11:INDEX(data!$C:$C,MATCH(9.99E+307,data!$A:$A))
C2: =SUMPRODUCT(--(INDEX(_Data,0,1)>=$A2),--(INDEX(_Data,0,1)<=$B2),--(INDEX(_Data,0,2)>=$A2),--(INDEX(_Data,0,2)<=$B2),INDEX(_Data,0,3)) D2: =SUMPRODUCT(--(INDEX(_Data,0,1)>$B2),--(INDEX(_Data,0,2)>=$A2),--(INDEX(_Data,0,2)<=$B2),INDEX(_Data,0,3))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Firstly thanks for coopration.
I have little quary regarding name define explain meaning of 9.99e+307
It is simply a Big Number... 9.99E+307 being 9.99*10^307 ... it used to determine the row in which the last number is found within specified range.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks