Count number of row for Particular month

[Praveen kumar.M]

Hi,

I have a sheet whit the following data:

DATE Subject
April 5, 2008 citi
April 5, 2008 citi
April 6, 2008 citi
April 4, 2008 bank
April 6, 2008 bank
May 5, 2008 citi
May 7, 2008 citi
May 5, 2008 bank
May 5, 2008 bank

*Note : DATE is formatted as ‘4-5-2008’ to ‘April 5, 2008’

Question:
I need to count number of rows for a particular month,
Ex:
For April : 5 and May : 4
And
For April : 3 for citi and 2 for bank and May : 2 for citi and 2 for bank

Please help me….

Thanks,
Praveen kumar.M

 

[Gaurav]

Try this for April and Citi.

=SUMPRODUCT((MONTH(A1:A4)=4)*(B1:B4="CITI"))

for may, change 4 to 5, june 6, july 7 and so on.

 

[Ron Coderre]

Have you considered using a Pivot Table?

With your sample data in A1:B10...

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel.........Click [Next]
Select your data...Click [Next]
Click the [Layout] button

ROW:
---Drag the DATA field here
---Drag the Subject field here, too

COLUMN: (Leave this area empty)

DATA: Drag the Subject field here

Click [OK]
Select where you want the Pivot Table...Click [Finish].

Almost done...That will list the count of each Subject by Date.

Right-Click on the DATE field
Select: Group and Show Detail....Group
Check: By Months
Click [OK]

Now...The Pivot Table will list the count of each Subject by Month.

To refresh the Pivot Table, just right click it and select Refresh Data

Pivot Table Links:
http://www.nickhodge.co.uk/gui/datamenu/pivottablereport.htm
http://www.contextures.com/tiptech.html

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Marcelo]

Helo

to the months use

=sumproduct(--(month(a2:a10)=4)) for April
=sumproduct(--(month(a2:a10)=5)) for May

you can use an auxiliar cell to replace 4(Apr) or 5(May) on the formula

to months/citi or bank

=sumproduct(--(month(a2:a10)=4)*(b2:b10="citi"))
also here you can use an auxiliar cell to month and the second variable.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Praveen kumar.M" escreveu: