# Using Frequency Formula to find frequency of number 1s, if row has Specific Location "USA"

1. ## Using Frequency Formula to find frequency of number 1s, if row has Specific Location "USA"

I have a spreadsheet with the following columns:

Name Location Rating If
James France Above Target 1
Mary UK Target 2
Sam USA Above Target 1
Jane USA Above Target 1

On the next three sheets I have the following tables created to populate graphs using the values from the frequency equation for the number of ratings for each Location.

Rating Value Frequency
Below Target 1
Target 2
Above Target 3

For the first sheet I am using the Frequency formula to populate how many 1s are on the first spreadsheet and how many 2s and 3s for Canada

But I have to sort the first spreadsheet by the location column and only include the rows with Canada as a location.

Is there a way that I can populate the frequency formula to search all rows in the first spreadsheet and return the number of 1s, 2s 3s for a specific location? If the first spreadsheet is sorted another way the graphs will be incorrect.

Hope this is clear. I have attached a sample file Excel Location_Rating Graph tabs.xlsx

Thanks

2. ## Re: Using Frequency Formula to find frequency of number 1s, if row has Specific Location "

Create a pivot table
And then make the graph from a pivot table

3. ## Re: Using Frequency Formula to find frequency of number 1s, if row has Specific Location "

The data will likely change does this translate to the pivot table data? Or will I have to create the pivot table everytime?

4. ## Re: Using Frequency Formula to find frequency of number 1s, if row has Specific Location "

Pivot table don't update automaticaly
Do it manually (button) or it can be done with VBA

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1