+ Reply to Thread
Results 1 to 7 of 7

formula that can count number of orders booked on a Date ignoring duplicates

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    Tijuana Mexico
    MS-Off Ver
    Excel 2010
    Posts
    6

    Red face formula that can count number of orders booked on a Date ignoring duplicates

    i cant seem to do a countif formula that can provide me a total amount or orders booked on a date and ignore duplicate order entries honestly this is my firts time using excel and this forum, and i dont know if this is the correct way of asking this situation im encountering.

    attached is an workbook

    i need a formula that could take the criteria of a date and order numbers and provide me total number of orders booked on a date and ignore duplicate order numbers

    apologies for the redundancy.

    test.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: formula that can count number of orders booked on a Date ignoring duplicates

    Is this what you're looking for? I'm assuming you're trying to know the total UNIQUE orders booked in 1 DAY
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-20-2013
    Location
    Tijuana Mexico
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: formula that can count number of orders booked on a Date ignoring duplicates

    thanks for your prompt response i tried it, and i cant make it work

  4. #4
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: formula that can count number of orders booked on a Date ignoring duplicates

    I'm sorry for not telling you earlier. This is an Array...thus, you have to press Ctrl + Shift + Enter at the formula bar to make it curly brackets { }

  5. #5
    Registered User
    Join Date
    02-20-2013
    Location
    Tijuana Mexico
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: formula that can count number of orders booked on a Date ignoring duplicates

    ok i think i have it let me try it again ill keep you posted, sorry went out for lunch break

  6. #6
    Registered User
    Join Date
    02-20-2013
    Location
    Tijuana Mexico
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: formula that can count number of orders booked on a Date ignoring duplicates

    Dluhut youre awesome thanks alot for helping me out this worked perfectly

    where can i find information on how to know how this works and into detail

    if you dont mind me asking?

  7. #7
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: formula that can count number of orders booked on a Date ignoring duplicates

    I think you can find the information here at this forum. If not through googling with keywords such as "excel array", "excel sum array" etc

    To be honest, I'm not an excel expert, thus won't be able to give a clear explanation. But I'll try my best.

    Please Login or Register  to view this content.
    As I mentioned earlier, this uses the array function, for all functions in it. SUM function, as it suggest, will sum everything that the value of IF gives.

    The IF function, looks at the dates on Sheet2 and 'see' if it's the date of the one that you're interested in. If it's true, then it'll calculate, if not, zero value.

    The calculate value of the true value from the IF statement, returns {1, 1, 0, 0, etc} Thus, when combined, with the sum, i.e. {1, 1}, gives us 2

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1