# Counting words in one column to count words in another column

1. ## Counting words in one column to count words in another column

I am currently having an issue in counting data in one column and then for it to count the sum of a word of another colum

eg.

A B

Accomodation New
Accomodation New
Accomodation Current
Travel New
Travel Current
Car New
Car New

What i want the formula to do is to count column A for Accomodation and then throught set criteria for it to tell me how many are new, I want to pull this data into another sheet to create a graph, so I will be able to create the graph the data tell me I have 12 Accomodations and 6 are New and 6 are current..

I have been trying this formula:

=SUMPRODUCT(--('Contract Master File v1'!B11:B88="accomodation"),--('Contract Master File v1'!L11:L88="new"))

I already have a countif formula to give me the totals of a just cant seem to get the detail??

2. ## Re: Counting words in one column to count words in another column

Hi,

maybe you could use a Pivot Table and associated graph?

3. ## Re: Counting words in one column to count words in another column

Hello

Rather than using formula it sound like it would be better to create a pivot table.

If you have column A and B as the row values (in that order) and then have A as a count in the data section it will give you a count of each.

hope this helps

Cheers

Jim

4. ## Re: Counting words in one column to count words in another column

will give that a try!!

5. ## Re: Counting words in one column to count words in another column

I am finding it hard to represent the data how I would like it, using a pivot table, could be as I have never used pivot tables. To keep it simple for myself I think the formula way could be easier, I just need to try find the correct formula!! But cheers for the help

6. ## Re: Counting words in one column to count words in another column

It might be an idea to post a sample file and outline your desired output ... it might be the case that formulae are required, however, if a Pivot will/can suffice then you should use it.

Attached is a very basic demo. based on your initial post.
(also utilises a Dynamic Named Range as source for Pivot such that it should always report the latest data - you will need to Refresh the Pivot as and when you modify the source data however [right click - Refresh])

7. ## Re: Counting words in one column to count words in another column

Im going to give the pivot another retry with the example does make it more simple than when I was looking at all the data.

##### Users Browsing this Thread

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