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
Configuration: Windows XP Firefox 3.0.10
You can get the file from here ....... feel free to ask , in case of any query
|
=SUMPRODUCT(--(YEAR('Claim Report'!O1:O25000)=YEAR($C$14))*--(MONTH('Claim Report'!O1:O25000)=A16),--('Claim Report'!R1:R25000=$D$15))
|
Compare 2 records in same list excel 07 Convert date to month and year in excel Create a unique list from two columns in excel Dos list files in folder excel Downloading list from combo box in excel Excel 2007 lookup a list and match text in a cell Excel add one month from today Excel count number of days this year Excel macro find list of files with macro Export excel month year format problem Find the first number not equal to zero in a given column in excel How to convert numeric date into month and year in excel How to divide each number in a list by the same number in excel How to list 1 to 100 in excel How to turn notepad list into an excel columns Import email list from excel to outlook Mysql list past month dates Show rows based on list value in excel