Forum

Notifications
Clear all

Count Values by Year

5 Posts
2 Users
0 Reactions
75 Views
(@suzanneneedshelp)
Posts: 2
New Member
Topic starter
 

I can't figure out the right formulas to use for 2 calculations.

1. I'm trying to count the number of times the source is Radio in 2016.

2. I'm also trying to count the number of occurrences in each month of 2016. 

A sample spreadsheet is attached.

 
Posted : 26/11/2018 11:09 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Suzanne

I would suggest you add 2 helper columns. One for Year and another for Month.

You can then use the SUMIFS and COUNTIFS to sum/count what you require.

Hope this helps.

Sunny

 
Posted : 26/11/2018 11:59 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Here is another method using Pivot Tables.

I have tided up some invalid dates (29/2/2017) in your data.

Sunny

 
Posted : 27/11/2018 2:18 am
(@suzanneneedshelp)
Posts: 2
New Member
Topic starter
 

I've never used Pivot Tables before. They seem very elegant.

In the other solution, I get the attached error message. Whether I update or not, all the values in the formula cells turn to 0. Any ideas on how to avoid this?

Thanks for your help.

Image.JPG

Suzanne

 
Posted : 27/11/2018 3:34 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Suzanne

The error is from your file. Not from the formulas.

Column F is linked to 'C:UsersThe Girls 2Google Drive3StormbornClinical Documents[UFE Call Log.xlsx]Pt Tracking'!AB2

Just copy column F and then paste as value. This should solve the problem.

Hope this helps.

Sunny

 
Posted : 27/11/2018 8:33 pm
Share: