+ Reply to Thread
Results 1 to 11 of 11

Count unique occurances formula

  1. 04-20-2017, 04:48 PM #1
    lreed
    lreed is offline
    Registered User
    Join Date
    10-30-2013
    Location
    Boise, ID
    MS-Off Ver
    Excel 2010
    Posts
    36

    Count unique occurances formula

    Good Afternoon

    I am working on creating a spreadsheet track how many times what types of contacts are made with each room number over a period of time. I have the formula completed and working to count all issues, but I want the formula to exclude duplicate issues- if it occurs multiple times in the same month. I am attaching a sample workbook. There is the summary tab which is where I want the formula to go, and a data entry sheet where in essence the data is recorded by month. I have tried a couple different if and count if formulas but am not getting the results I want.

    Any help is greatly appreciated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-20-2017, 05:57 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Count unique occurances formula

    Good afternoon.

    Try this formula in B4 of 'Total' and fill down. Copy and paste the range into cells D4, F4, H4, J4 and L4.

    =IFERROR(INDEX(INDIRECT("'"&B$3&"'!a4:a100"),MATCH($A4,INDIRECT("'"&B$3&"'!C4:C100"),0)),0)
    Dave
    Reply With QuoteRegister To Reply
  3. 04-20-2017, 05:58 PM #3
    loginjmor
    loginjmor is offline
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Count unique occurances formula

    Hi -

    In your summary tab, Cell C4, copy and paste this formula:

    =IF(ISERROR(VLOOKUP($A4,'Month 1'!$C$4:$C$13,1,FALSE)),0,1)

    Then copy it down. This just does a VLOOKUP for the Service/Activity. If it's not in the list, it returns a 0. If it's in the list (regardless of how many times) it returns a 1.

    Hope this helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
    Reply With QuoteRegister To Reply
  4. 04-20-2017, 06:01 PM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Count unique occurances formula

    I forgot to attach the file in post#2.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 04-20-2017, 06:02 PM #5
    loginjmor
    loginjmor is offline
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Count unique occurances formula

    Flameretired's post is more robust than mine in that it automatically switches to the different monthly sheets as you copy it across. Mine you would have to re-specify the data sheet in the VLOOKUP search array. In other words 'Month1'! would have to change to 'Month2'! etc.
    Reply With QuoteRegister To Reply
  6. 04-20-2017, 06:23 PM #6
    lreed
    lreed is offline
    Registered User
    Join Date
    10-30-2013
    Location
    Boise, ID
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Count unique occurances formula

    This works perfectly, Thank you!
    Reply With QuoteRegister To Reply
  7. 04-20-2017, 06:36 PM #7
    lreed
    lreed is offline
    Registered User
    Join Date
    10-30-2013
    Location
    Boise, ID
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Count unique occurances formula

    So after futher playing with this, if you change the duplicate room number it does not count it as a unique contact. It needs to... because it is a different person......
    Reply With QuoteRegister To Reply
  8. 04-20-2017, 06:39 PM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Count unique occurances formula

    So if one of the Tax Preparation Services in Month 1 was Unit # 251 Tax Preparation Services in 'Total' would be 2?

    Edit
    BTW: Is that the purpose of the Unique Counts columns?
    Last edited by FlameRetired; 04-20-2017 at 07:07 PM.
    Reply With QuoteRegister To Reply
  9. 04-20-2017, 07:44 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Count unique occurances formula

    Try this formula in B4 an fill down.

    =SUMPRODUCT(($A4=INDIRECT("'"&B$3&"'!C4:C100"))/COUNTIFS(INDIRECT("'"&B$3&"'!C4:C100"),INDIRECT("'"&B$3&"'!C4:C100")&"",INDIRECT("'"&B$3&"'!b4:b100"),INDIRECT("'"&B$3&"'!b4:b100")&"",INDIRECT("'"&B$3&"'!a4:a100"),INDIRECT("'"&B$3&"'!a4:a100")&""))

    I put several duplicates and unique combinations in 'Month 1' to test this. See if it does what you want.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 04-21-2017, 12:36 PM #10
    lreed
    lreed is offline
    Registered User
    Join Date
    10-30-2013
    Location
    Boise, ID
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Count unique occurances formula

    yes that is second formula works. I need the unique count to be able to recognize another room, but not the exact same room.... This is perfect thank you so much for all your help!
    Reply With QuoteRegister To Reply
  11. 04-21-2017, 07:29 PM #11
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Count unique occurances formula

    Glad to help. Thank you for the feedback, and thank you for marking this thread [SOLVED].
    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)

Similar Threads

  1. 90 day rolling formula to count occurances.
    By alaz88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-28-2023, 02:35 PM
  2. Help with formula to count number of occurances of texts from visible range
    By rv02 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-07-2016, 02:07 PM
  3. [SOLVED] Count unique occurences in one column with unique occurances in another column
    By 21stCenturyLessons in forum Excel General
    Replies: 5
    Last Post: 07-12-2014, 08:44 AM
  4. [SOLVED] Count the number of unique values (occurances) in a range.
    By angelopc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-13-2013, 10:36 AM
  5. Count occurances of date across multiple columns & display # of occurances for each column
    By masi10 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-11-2012, 03:38 AM
  6. How to count unique occurances linking two columns
    By nebula786 in forum Excel General
    Replies: 1
    Last Post: 06-29-2007, 01:19 AM
  7. Count # of unique occurances
    By sharder in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-29-2006, 12:45 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1