+ Reply to Thread
Results 1 to 6 of 6

Count Unique instances across several Columns

  1. 05-14-2012, 12:18 AM #1
    Leigh.Odonnell
    Leigh.Odonnell is offline
    Registered User
    Join Date
    05-13-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    2

    Count Unique instances across several Columns

    I have a spreadsheet with several columns (20 odd) and several thousand rows (10187!)
    See "data" Tab for a watered down version
    I need to count the total number of shifts worked (for a given period - say monthly)

    Ie: in the data sheet Equip # 111 - how many shifts did it work in April , then May

    There are several line descriptions for each date & shift worked
    Some work day & night shift - some only work days

    I would like a formula - Not sorting/filtering -( I can do that) but I am doing this all the time & would like a better more robust way to calculate.

    Thanks if you can help.
    Leigh
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-14-2012, 02:01 AM #2
    Russell Dawson
    Russell Dawson is offline
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: Count Unique instances across several Columns

    Hi

    I've come up with the attached which includes this array formula -

    =SUM((C2:C50=$J$2)*(H2:H50=$K$2)*(D2:D50=$L$2)*(F2:F50=M2))

    So enter with Ctrl-Shift-Ent

    It requires a helper column for the month number and requires you to input month number.
    Updated with improved s/s to show all permutations
    Attached Files Attached Files
    Last edited by Russell Dawson; 05-14-2012 at 03:11 AM. Reason: Updated attachment
    If I helped, please don't forget to add to my reputation. (click on the star below the post)

    If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.

    Failure is not falling down but refusing to get up.
    Reply With QuoteRegister To Reply
  3. 05-14-2012, 02:12 AM #3
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count Unique instances across several Columns

    Hi Welcome to the forum.

    A SUMPRODUCT, version..

    Start date =J1

    End date =J2

    For all "111"

    Please Login or Register  to view this content.
    For Day
    Please Login or Register  to view this content.
    For night
    Please Login or Register  to view this content.
    Is this, works for you?
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  4. 05-14-2012, 02:40 AM #4
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Count Unique instances across several Columns

    see if this helps. with this, you could find HOURS for just 111, or 111 + Day shift, or just DAY shift, or 111 + April... try it out.

    you have some white spaces in your data, which scupper calculations.
    Attached Files Attached Files
    Last edited by icestationzbra; 05-14-2012 at 02:46 AM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -
    Reply With QuoteRegister To Reply
  5. 05-14-2012, 04:52 PM #5
    Leigh.Odonnell
    Leigh.Odonnell is offline
    Registered User
    Join Date
    05-13-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Count Unique instances across several Columns

    Thanks , but I need the number of "shifts" Calculated.
    If equip # 111 has 3 description lines for 1/4/2012 on day shift = 1 shift
    If equip # 111 has 3 description lines for 1/4/2012 on day shift and 2 description lines for night shift = 2 shifts
    If equip # 111 has 3 description lines for 1/4/2012 on day shift & 3 description lines for 2/4/12 day shift = 2 shifts
    Any ideas?

    Thanks Leigh
    Reply With QuoteRegister To Reply
  6. 05-14-2012, 06:05 PM #6
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Count Unique instances across several Columns

    Try this, with CTRL+SHIFT+ENTER, rather than just ENTER

    Please Login or Register  to view this content.
    Of Course should have more intellectual ways.

    Change the Equipment, Start & End Dates.
    Last edited by Haseeb Avarakkan; 05-14-2012 at 06:09 PM.
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1