+ Reply to Thread
Results 1 to 6 of 6

Counting how many observation occur a given number of times under conditions

  1. #1
    Registered User
    Join Date
    07-23-2014
    Location
    Warsaw, Poland
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Counting how many observation occur a given number of times under conditions

    I need to calculate the number of observations that occur once and twice in the database, without having the value of the observation. I've found some solutions for this already, but only for one condition and I found it difficult to convert the formulas to my needs. A typical data entry looks similar to this:
    HTML Code: 
    What I want to do is to find how many clients from a certain group have a certain number of products. My shot this, based on a formula found online, was

    Please Login or Register  to view this content.
    where I wanted to see how many clients have two products with numbers 1,2,3,4,6 from group 5.
    Unfortunately it failed miserably and I'm stuck.

    I would be grateful for every help!

  2. #2
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Counting how many observation occur a given number of times under conditions

    So the answer for group 5 in your example would be 2?

    If not can you give some more examples with expected results?
    Last edited by TheCman81; 07-23-2014 at 10:25 AM.
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

  3. #3
    Registered User
    Join Date
    07-23-2014
    Location
    Warsaw, Poland
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Counting how many observation occur a given number of times under conditions

    Actually it should be 1, because the cells in question are:
    HTML Code: 
    And since product number "7" is not taken into account only client 4 can be counted as 1 person from group 5 that appears twice.
    My bad for not checking the specific combination because for some reason my formula does give 1 as a result but when I add the missing 7 to the formula, expecting 2, I get 0.

  4. #4
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Counting how many observation occur a given number of times under conditions

    Can you try this array formula, entered with Ctrl Shift Enter

    This is a stab in the dark

    =SUM(IF(FREQUENCY(IFERROR(IF(IF(COUNTIF($B$2:$B$20,$B$2:$B$20)>=2,IF($C$2:$C$20=E1,MATCH($A$2:$A$20,{1,2,3,4,5,6},0))),$B$2:$B$20),FALSE),ROW($B$2:$B$20)-ROW($B$2)+1)>=2,1))

    Is your criteria always 2 products and between 1-6

    Edit: Forgot to mention E1 highlighted in red contains the group number you can to check against
    Last edited by TheCman81; 07-23-2014 at 11:15 AM.

  5. #5
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Counting how many observation occur a given number of times under conditions

    Can I assume your products won't be numbered 1 through 10?

    Maybe you could set up a table with a list of products you want to include in your search (see attached)

    Count Observations v1.xlsx

  6. #6
    Registered User
    Join Date
    07-23-2014
    Location
    Warsaw, Poland
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Counting how many observation occur a given number of times under conditions

    Yes, it is perfectly doable and they normally have text names, but in what way does it help? I feel quite lost with this problem.
    The criteria is 1, 2 or 3 products bought, and always 16 possible out of ... about 100 maybe.
    When I run my initial formula on a larger database it usually freezes, so it might as well be an issue of the processing capacity.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Counting the number of times in a column certain date & time conditions are met
    By Pebbles33 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-25-2013, 09:31 AM
  2. [SOLVED] counting how many times it occur
    By Elainefish in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-05-2013, 04:30 AM
  3. Need help returning the number of times 2 events occur simultaneously
    By dudakia in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-30-2012, 06:49 PM
  4. Need help returning the number of times 2 events occur simultaneously
    By dudakia in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-20-2012, 09:05 PM
  5. [SOLVED] Flagging formula for file names that do not occur a set number of times
    By njmiller31 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2012, 09:34 PM

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