Counting words in one column to count words in another column

**stuart1133** · 12-16-2010, 04:10 AM

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??

**arthurbr** · 12-16-2010, 04:21 AM

Hi,

maybe you could use a Pivot Table and associated graph?

**JimmyA** · 12-16-2010, 04:22 AM

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

**stuart1133** · 12-16-2010, 04:23 AM

will give that a try!!

**stuart1133** · 12-16-2010, 04:59 AM

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

**DonkeyOte** · 12-16-2010, 05:14 AM

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])