COUNT rows with specific text

Rauri

New Member
Joined
Aug 29, 2014
Messages
3
Hi,

I have a spreadsheet containing data for various departments. Example:

Column A contains names of departments.
column B contains different items attached to those departments. Some departments may have 10 items attached to them, others may have 24 items and yet others 22 items etc. Each item is contained in a row. typically,

Row 1 DeptA Swimming
Row 2 DeptA Biking
Row 3 DeptA Rock climbing
.
.
.
Row 24 DeptA Writing
Row 25 DeptB Biking
.
.
Row 36 DeptB Bingo
Row 37 DeptC Bingo
Etc

What formula do I use to count number of rows of items per department?

Thanks

Rauri
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Welcome to the Board!

There are a few different ways you could do this.
You could use a COUNTIF function (see: COUNTIF function - Excel).
You could also use Subtotals, or maybe a Pivot Table.
 
Upvote 0
You could use a pivot table with department as the column header and the items as the row header
 
Upvote 0
Hi
Many thanks for your reply. I cannot see how I can use COUNTIF. I have here a sample, where column C, with a formula, will provide the count of items per department. I do not wish to have to enter the criteria each time though.

thanks!

SAMPLE

DepartmentItemCOUNT
Dept274HewettAD_CLais37
Dept274HewettH_Flower
Dept274HewettH_FloweLa_HO
Dept274HewettS
Dept274CaulifloweLa
Dept274Catarina
Dept274Cx
Dept274DEPT
Dept274DI
Dept274HO
Dept274Dodo
Dept274Elf
Dept274PeaLas
Dept274TaLaLaagon
Dept274Geranium
Dept274Hibiscus_POLa
Dept274Hibiscus_LaES
Dept274KLaocus_Vittorio
Dept274Klox
Dept274Mint
Dept274MamoLa
Dept274Nana
Dept274Dodo
Dept274Dodo_ND_ViCatarina
Dept274Dodo_S
Dept274LaHOHewett
Dept274Laollo
Dept274Laamax
Dept274LaamaxC
Dept274LaamaxF
Dept274LaamaxLL
Dept274LaitaTYPE_Velo
Dept274LaSN
Dept274LaT
Dept274LaLaamsey
Dept274Spa
Dept274Wally_ALa_ViCatarina
Dept494HewettAD_CLais31
Dept494HewettH_FloweLa
Dept494HewettH_FloweLa_HO
Dept494HewettS
Dept494CaulifloweLa
Dept494Catarina
Dept494DEPT
Dept494HO
Dept494Dodo
Dept494Elf
Dept494PeaLas
Dept494TaLaLaagon
Dept494Geranium
Dept494Hibiscus_POLa
Dept494Hibiscus_LaES
Dept494KLaocus_Vittorio
Dept494Klox
Dept494Nana
Dept494Dodo
Dept494Dodo_ND_ViCatarina
Dept494Dodo_S
Dept494LaHOHewett
Dept494Laollo
Dept494LaILM
Dept494LaLL3
Dept494Laamax
Dept494LaamaxC
Dept494LaamaxF
Dept494LaitaTYPE_Velo
Dept494Spa
Dept494Wally_ALa_ViCatarina

<colgroup><col><col><col></colgroup><tbody>
</tbody>
Regards

Rauri

Welcome to the Board!

There are a few different ways you could do this.
You could use a COUNTIF function (see: COUNTIF function - Excel).
You could also use Subtotals, or maybe a Pivot Table.
 
Upvote 0
Let's say that you data starts on row 2 and goes goes down to row 100.
Then enter this formula in cell C2 and copy down for all rows. It will only show a number in the first row for each department.
Code:
=IF(A2<>A1,COUNTIF(A$2:A$1000,A2),"")
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top