+ Reply to Thread
Results 1 to 17 of 17

Colum count based ón values In colum a and b

  1. #1
    Registered User
    Join Date
    11-05-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    61

    Colum count based ón values In colum a and b

    Hey all hope you Can Help me

    I have 2 colums.

    Apple banana
    Orange Banana
    Apple. Orange
    Apple. Banana

    I need to find a function that count the number of times i have Apples In colum a while having banans In colum b. As we Can see In this ex it happens 2 times. I have tried countif and some sum product, however i cant seem to make it work.

    Thx In advance and hope you Can Help
    Last edited by 2001jesper; 11-05-2012 at 03:24 PM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Colum count based ón values In colum a and b

    Try =COUNTIFS(A1:A4,"apples",B1:B4,"banana")

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Colum count based ón values In colum a and b

    With your sample data in A1:B4
    this regular formula counts the "begins with" apple/banana combinations
    =COUNTIFS(A1:A10,"Apple*",B1:B10,"banana*")

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    11-05-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Colum count based ón values In colum a and b

    Well i works.. but for some reason i cant transfer this to my real problem. The data i have is downloaded from a data base, meaning it comes in a predefined form with "filters on". I can use count(XXX) and get an overall picture, but when using countifs(XXXX) to dig into the data, it returns an invalid message. Could this obstruct the use of the formula? it really wouldn't make any sense.

    But thx for the time spend, i appriciate it alot

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Colum count based ón values In colum a and b

    Perhaps post a small sample sheet stripped of any confidential information?

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Colum count based ón values In colum a and b

    If you can post a sample workbook that illustrates the issue, perhaps we'll be able to spot the problam.

  7. #7
    Registered User
    Join Date
    11-05-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Colum count based ón values In colum a and b

    well here it goes.

    Its entered into excel as a table, thats the only obstruction i can see. If copied and pasted as values -> np. However its not the solution i seek
    Last edited by 2001jesper; 11-05-2012 at 02:50 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Colum count based ón values In colum a and b

    2001jesper,

    Can you upload an EXCEL sample file? It's not easy to troubleshoot a picture
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  9. #9
    Registered User
    Join Date
    11-05-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Colum count based ón values In colum a and b

    didn't know i could do that.. give me a few minutes

  10. #10
    Registered User
    Join Date
    11-05-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    61

    Thumbs up Re: Colum count based ón values In colum a and b

    1 excel files uploaded

    i really appriciate your interest and thx in advance
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Colum count based ón values In colum a and b

    jesper,

    Can you replicate the problem on your v1.xlsm spreadsheet?

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Colum count based ón values In colum a and b

    Maybe with an pivot table.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  13. #13
    Registered User
    Join Date
    11-05-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Colum count based ón values In colum a and b

    Well i see why you ask that, cause the uploaded spreadsheet actually works - really dont understand why the "original" keeps bothering me. I guess iam unable to replicate the problem

    I really apriciate you taking your time to problem solve, but i guees this is a lost cause.

  14. #14
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Colum count based ón values In colum a and b

    Since you're using an Excel Table...Select the entire data section of the referenced columns so Excel can build proper table references.

    Using your posted workbook, this would be the formula:
    Please Login or Register  to view this content.
    Is that something you can work with?

  15. #15
    Registered User
    Join Date
    11-05-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Colum count based ón values In colum a and b

    Worked perfectly. Thank you so much and to all of you who spend a few minuttes on my problem.

  16. #16
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Colum count based ón values In colum a and b

    Glad you got sorted! Will you please mark your thread as "Solved" if you're satisfied that it is.

  17. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Colum count based ón values In colum a and b

    Glad i could help.

    Thanks for the reply.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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