• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for summarazing data based on inputs

N

nandhamnk

New Member
Dear All,


I need a summary as below by using formula.

Inputs are below:


Date Particulars Amount

28-09-2012 ECS/A1 abcdefgh 10000

30/09/2012 ECS/A3 asdefijgk 2000

10-10-2012 ECS/A2 peiourmfk 5000


output as below:


ECS Sep-12 Oct-12 Nov-12 Dec-12

A1 10000 (Answer)

A2 5000(Answer)

A3 2000(Answer)


Thanks for the support.
 
Hi nandhamnk,


Your data is present in Cells A1:C4 like bellow:

[pre]
Code: 
Date	       Particulars	        Amount
9/28/2012	ECS/A1 abcdefgh	        10000
9/30/2012	ECS/A3 asdefijgk	2000
10/10/2012	ECS/A2 peiourmfk	5000
Your output is present in E1:I4 like bellow:


[code]Sep-12	Oct-12	Nov-12	Dec-12
ECS/A1	10000	0	0	0
ECS/A3	2000	0	0	0
ECS/A2	0	5000	0	0
[/pre]
Enter this formula in F2 and drag to Right & Downward.


=SUMPRODUCT(($A$2:$A$4>=F$1)*($A$2:$A$4<G$1)*(LEFT($B$2:$B$4,6)=$E2)*$C$2:$C$4)[/code]


Note that all dates here are "True Dates"


Regards,

Faseeh
 
Hi nandhamnk,


See this file:


http://dl.dropbox.com/u/60644346/SumproductFunction.xlsx


Regards,
 
You must log in or register to reply here.
Back
Top