+ Reply to Thread
Results 1 to 5 of 5

Count Unique Numbers with IF statements

  1. 12-02-2012, 08:37 PM #1
    jj554
    jj554 is offline
    Registered User
    Join Date
    11-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    45

    Lightbulb Count Unique Numbers with IF statements

    This formula has me stumped.

    I have a table of data. Each entry has a date. I separate each part of the date to separate columns so there is a column for year, month, week ,and day. Those columns are referenced by named ranges: "YearCount", "MonthCount", "WeekCount", "DayCount".

    I'm trying to count the entries in the table that fall within a specific time period.

    For example:

    If I want to count the unique entries dated from last week, the formula would include...

    IF YearCount = YEAR(Today()) * MonthCount = MONTH(Today()-1) THEN Count the number of days there are in the data that fall in that time period (last month)

    I attached a sample worksheet with the named ranges included.

    Thanks for the help!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-02-2012, 08:49 PM #2
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Count Unique Numbers with IF statements

    do you NEED to use an if statement? Excel 2010 supports the sumifs function, which would better fit your requirements I believe..

    -Edit-
    if back compatibility is an issue, Sumproduct is also an option

    -Edit 2-
    or countifs, after re-reading OP
    Last edited by dredwolf; 12-02-2012 at 09:03 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post
    Reply With QuoteRegister To Reply
  3. 12-02-2012, 09:40 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Count Unique Numbers with IF statements

    see if the attached gives you what you want. i added 2 extra columns (yellow), you may not need you're year/month/day columns now. i used 30 days back from today (well actually 365/12) as the bottom limit, and yoday() as the upper limit. you can change this, or even put the upper and lower in thewir own cell and reference them, if you want
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 12-02-2012, 11:56 PM #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 Numbers with IF statements

    try the following ARRAY formula, it outputs a result of 4 for your sample data:

    Please Login or Register  to view this content.
    don't use Merged Cells; they are nothing but trouble. use Centred-Across-Selection formatting instead.

    Update:

    slightly shorter:

    Please Login or Register  to view this content.
    995
    Last edited by icestationzbra; 12-12-2012 at 07:27 PM.
    - 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. 12-07-2012, 02:46 AM #5
    jj554
    jj554 is offline
    Registered User
    Join Date
    11-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Count Unique Numbers with IF statements

    This did exactly what I needed it to. Thanks for the help!
    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)

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