list month from any given year in excelSolved/Closed

Question

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

mubashir aziz · Answer

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 .......

smd · Answer

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

mubashir aziz · Answer

=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 ....

smd · Answer

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

smd · Answer

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....

List month from any given year in excel

5 responses

Similar discussions

Subscribe To Our Newsletter!