+ Reply to Thread
Results 1 to 7 of 7

Counting words in one column to count words in another column

  1. #1
    Registered User
    Join Date
    12-16-2010
    Location
    brum, uk
    MS-Off Ver
    Excel 2003
    Posts
    4

    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. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Counting words in one column to count words in another column

    Hi,

    maybe you could use a Pivot Table and associated graph?

  3. #3
    Forum Contributor
    Join Date
    02-14-2007
    Location
    Chester, England
    MS-Off Ver
    2010 & 2016
    Posts
    312

    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. #4
    Registered User
    Join Date
    12-16-2010
    Location
    brum, uk
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Counting words in one column to count words in another column

    will give that a try!!

  5. #5
    Registered User
    Join Date
    12-16-2010
    Location
    brum, uk
    MS-Off Ver
    Excel 2003
    Posts
    4

    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. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

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

  7. #7
    Registered User
    Join Date
    12-16-2010
    Location
    brum, uk
    MS-Off Ver
    Excel 2003
    Posts
    4

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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