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

1. 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. 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

3. 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. 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. 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. 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. 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.

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

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