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

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.


"Praveen kumar.M" <Praveen wrote in
message ...

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

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/datam...ablereport.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)

"Praveen kumar.M" <Praveen wrote in
message ...

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

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:


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