List month from any given year in excel [Solved/Closed]

Report
-
 ani -
Hello,
i 0got a spreadsheet (data) as below
date status
1-jan-1975 approved
1-feb-1980 suspended
5-mar-1985 rejected
10-oct-1985 suspended
29-Oct-2004 approved

got a report sheet where the summary is listed as below
count for the year - (year)
month approved suspended rejected


based on the year the user types in the (year cell)...e.g. 2009 the months should be generated below the month column and below each status e.g. approved it should get the total count of approved status for the month of january 2009 from the data sheet and count for suspended for the month of jan . likewise count for all months according to their status.

i cud do the above by a macro...but like to use functiions if possible for this.

any help wil b much appreciated.
thanks

5 replies

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
213
you can get the file from here ....... feel free to ask , in case of any query

http://d01.megashares.com/dl/c16deb5/approved-rejected.xls


PS actually many sites have been blocked by our organization so you or anyone else know some nice site to upload file please do let me know .... I've tried almost 50 sites and only this was not blocked .......

thanks alot dude...m trying to work it out...but getting 0 as count. whereas it shud be 9


=SUMPRODUCT(--(YEAR('Claim Report'!O1:O25000)=YEAR($C$14))*--(MONTH('Claim Report'!O1:O25000)=A16),--('Claim Report'!R1:R25000=$D$15))

u can try www.sendthisfile.com for free upload
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
213
=SUMPRODUCT(--(YEAR('Claim Report'!O1:O25000)=YEAR($C$14))*--(MONTH('Claim Report'!O1:O25000)=A16),--('Claim Report'!R1:R25000=$D$15))


Check your Cell Reference YEAR($C$14) & $D$15 ..... Or check that you are entering date in proper format ....

below site is also blocked as i'm getting below info ....



Default Web Filtering Policy Blocked site category:File Host,Internet Services

I think if some sites don't have these parameters then i can share it ..... or i can share my files on some valuable forums .....

anyway check your formula again and do let me know ....

hi...can u give me ur email id...so i can send u the file.
hi dude,

its working now...very silly of me as it was referencing the wrong sheet name...which was quite similar to another worksheet name

thanks a ton....