CODEWITHSUNDEEP
excelfunctioncells
M.Stair
M.Stair

Reputation: 31

Need to compare cells in Excel

I need to compare two cells and then get a count for everything. It would be best just to show you.

      1                2
    A Schools          Details
    B Example school   L
    C Example school   M
    D Example school   L
    E Second school    L

So that is a very basic form of my table. There are around 56 schools and about 12 detail letters. The goal here is to get a count of all the "L" details that belong to "example school" all the "M" details that belong to "example school" and the same for the "second school"

I would like to end up with something like 

    Example school: 200 L details
    Example school: 50 M details
    Second school: 90 R details

Hopefully I made my point clear enough here! Thank you in advance for any help.

Upvotes: 0

Views: 66

Answers (2)

Howard Renollet
Howard Renollet

Reputation: 4739

So, here is how you would do this in a basic PivotTable:

I've created an sample data set like in your example

Now, go to your Insert menu and click PivotTable

enter image description here

Select the cell range for the PivotTable and click OK

enter image description here

You now have a PivotTable in a new tab:

enter image description here

Check Schools and Details to add them to your PivotTable

enter image description here

enter image description here

Now move Details from the Row Labels block to the Column Labels block, and drag another instance of Details to the Values block. The default value is count.

enter image description here

It is really worth it to learn how to effectively use PivotTables. I use these regularly to very quickly compare hundreds of thousands of rows and over a hundred columns of data, and it's so easy to manipulate the results to meet your needs, or create a PivotChart from the data. Our executive team loves how the charts and tables look when I put together a report for them.

Upvotes: 2

PermaNoob
PermaNoob

Reputation: 869

If you have excel 2007 or newer, you could use =countifs("A:A","Example School","B:B","L") for each one of those. That formula would give you the number of L's that Example School had.

Then you just change the L to M or any other one for all the details for Example School. Rinse and repeat for second school.

Upvotes: 2

Related Questions