Counting in excel tables

Resolved · Low Priority · Version 2007

Mon 25 Nov 2013, 16:08 Delegate James said...

James has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

Counting in excel tables

How can I make a Pivot table (or other function) count the number of times a value appears across different columns?

Tue 26 Nov 2013, 23:58 Trainer Gary said...

RE: Counting in excel tables

Hi James

Thanks for getting in touch. If it's something across several columns you might want to use a COUNTIF function.

A COUNTIF function has the structure:

=COUNTIF(range, criteria)

In range you can specify the cells with which to scan. The criteria is what you're searching for. Examples include:

=COUNTIF(A5:J50, "test") counts the occurrences of the word 'test'

=COUNTIF(A5:J50, 42) counts the occurrences of the number 42

=COUNTIF(A5:J50, ">101") counts all numbers greater than 101

I hope this gives you some hints, if you need more help please let us know.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 28 Nov 2013, 19:49 Delegate James said...

RE: Counting in excel tables

Hi. That's great, many thanks.
What I'm really looking for is something akin to a pivot table but which counts the number of times any value appears across different columns. For context, I might not know what value I'm looking for, I just need to know which values are cropping up. Is there a way to make Pivot tables do that? Thanks, James

Fri 29 Nov 2013, 12:08 Trainer Gary said...

RE: Counting in excel tables

Hi James

Thanks for your reply. A PivotTable will definitely do that, you should be able to arrange the items you are looking for in the Row section, and then another field in the Values section.

You will then need to change the field in the Values section: click on the field name in the Values box, then Value Field Settings. Choose Count and OK, and the PivotTable should display a count of how many times an item has featured.

It's difficult to be specific without seeing your data but this should point you in the right direction.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Change the default location for opening and saving spreadsheets

If you are always opening spreadsheets from and/or saving documents to a specific location that is not My Documents, save time by setting this folder as the default for opening files from and saving files to.

Here's how:
1. Go to Tools - Options.

2. Select the General tab.

3. Enter the pathname of the folder you wish to make the default in the Default File Location box (hint: it will be easier to use Windows Explorer to navigate to this folder, then copy and paste the pathname from the address bar at the top of the Windows Explorer screen).

4. Click OK.

You have now changed the default folder for opening and saving spreadsheets.

View all Excel hints and tips