Count rows based on criteria in MS Excel

질문

• Hi All,

I have an excel with following data in sheet 1

I wanted to generate a report in sheet 2 with following data as shown below

If  any one can help me on this, would be really great

Thanks n Regards

A.Salam

2012년 6월 9일 토요일 오후 3:30

답변

• In Sheet2, enter formulas as shown and copy across.

Formulas are:

For Total NCN (entered in cell B2)

=COUNTIF(Sheet1!\$B:\$B,Sheet2!B\$1)

For Closed NCN (Entered in cell B3)

=COUNTIFS(Sheet1!\$B:\$B,Sheet2!B\$1,Sheet1!\$E:\$E,"C")

Note that these refer to Cell B1 to obtain Department

Ed Ferrero
www.edferrero.com

2012년 6월 10일 일요일 오전 7:10

모든 응답

• This is pretty easy. What specifically are you having trouble with?
2012년 6월 9일 토요일 오후 7:32
• Hello,

I am not much familiar with Excel functions / VBA.

2012년 6월 10일 일요일 오전 6:09
• In Sheet2, enter formulas as shown and copy across.

Formulas are:

For Total NCN (entered in cell B2)

=COUNTIF(Sheet1!\$B:\$B,Sheet2!B\$1)

For Closed NCN (Entered in cell B3)

=COUNTIFS(Sheet1!\$B:\$B,Sheet2!B\$1,Sheet1!\$E:\$E,"C")

Note that these refer to Cell B1 to obtain Department

Ed Ferrero
www.edferrero.com

2012년 6월 10일 일요일 오전 7:10
• Thanks a lot.....working fine

Regards

A.Salam

2012년 6월 10일 일요일 오전 10:03