+ Reply to Thread
Results 1 to 8 of 8

Help counting unique values from column meeting multiple criteria

  1. #1
    Registered User
    Join Date
    04-01-2012
    Location
    South Wales, UK
    MS-Off Ver
    Excel 2003
    Posts
    4

    Help counting unique values from column meeting multiple criteria

    Hi,
    I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.

    I have tried different formulas, but fast running out of time and I really need some help with this final piece of work.

    I have attached a modified sample from the main spreadsheet to hopefully help understand my predicament.

    I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.

    Thanks for any help you can give me.
    Mark
    Attached Files Attached Files

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help counting unique values from column meeting multiple criteria

    I didn't download your file. It's way too big for me!

    Try this array formula**:

    =SUM(IF(FREQUENCY(IF(Mon!$N$3:$N$4000="AM",IF(Mon!$O$3:$O$4000=D4,IF(Mon!$B$3:$B$4000="mon1",MATCH(Mon!$C$3:$C$4000,Mon!$C$3:$C$4000,0)))),ROW(Mon!$C$3:$C$4000)-ROW(Mon!$C$3)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    04-01-2012
    Location
    South Wales, UK
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Help counting unique values from column meeting multiple criteria

    Tony
    Thank you very much for getting back to me with this formula, I tried it and it worked fantastic thank you. I thought I would be clever enough to use the same formula on the next cell, where I needed to do exactly the same but add the following into the one you sent but it keeps failing:

    (Mon!$P$3:$P$4000="")

    =SUM(IF(FREQUENCY(IF(Mon!$N$3:$N$4000="AM",IF(Mon!$O$3:$O$4000=D4,IF(Mon!$B$3:$B$4000="mon1",MATCH(Mon!$C$3:$C$4000,Mon!$C$3:$C$4000,0)))),ROW(Mon!$C$3:$C$4000)-ROW(Mon!$C$3)+1),1))

    This is meant to add a filter to select only those cells where the cell is blank (attended), so I will be able to count the number of unique records that meet this added criteria. I really do not understand the formula you have given me well enough to know what is wrong.
    If you can help again I would really appreciate it.

    Thank you

    Mark

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help counting unique values from column meeting multiple criteria

    Maybe this...

    =SUM(IF(FREQUENCY(IF(Mon!$N$3:$N$4000="AM",IF(Mon!$O$3:$O$4000=D4,IF(Mon!$B$3:$B$4000="mon1",IF(Mon!$P$3:$P$4000="",MATCH(Mon!$C$3:$C$4000,Mon!$C$3:$C$4000,0))))),ROW(Mon!$C$3:$C$4000)-ROW(Mon!$C$3)+1),1))

    Still array entered.

  5. #5
    Registered User
    Join Date
    04-01-2012
    Location
    South Wales, UK
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Help counting unique values from column meeting multiple criteria

    Tony
    How can I thank you enough? Fantastic, works perfectly, thank you so much. Would you be able to explain the formula to me and what it is doing do I can understand how to pit one together on my own in the future? Why the +1 the ,0 etc for eg.
    Again thank you so much

    Mark

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help counting unique values from column meeting multiple criteria

    Here's an explanation I wrote at another forum.

    The principle is the same except that your application has more conditions (using more nested IF functions).

    http://www.mrexcel.com/forum/showpos...7&postcount=10

  7. #7
    Registered User
    Join Date
    04-01-2012
    Location
    South Wales, UK
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Help counting unique values from column meeting multiple criteria

    Tony

    Many thanks for your help with this and for the link which explains everything perfectly.

    Take care

    Mark

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help counting unique values from column meeting multiple criteria

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. Replies: 5
    Last Post: 04-20-2012, 09:47 AM
  2. Counting unique column entries while also meeting other criteria
    By Mom2a* in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-04-2012, 05:21 AM
  3. Frequency of unique values meeting multiple criteria
    By systemsteve in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2008, 09:50 AM
  4. Counting values meeting multiple criteria.
    By Kurtis in forum Tips and Tutorials
    Replies: 6
    Last Post: 10-31-2007, 06:52 PM
  5. Counting values meeting multiple criteria
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 06-12-2005, 03:06 AM

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