List month from any given year in excel

Solved/Closed
smd - Jun 4, 2009 at 01:40 AM
 ani - Jul 13, 2009 at 11:25 PM
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 responses

mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
Jun 4, 2009 at 02:59 AM
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 .......

0
dfgfdg
0
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
0
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
Jun 4, 2009 at 05:36 AM
=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 ....

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

Didn't find the answer you are looking for?

Ask a question
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....
0