Count If help with Unique Dates

Hi,

Thank you in advance for some expert help! I'm completely stuck, as I'm looking to Count unique dates with an IF formula that basically works into the formula specific customers. I have attached a sample spreadsheet with minimal data, and highlighted in yellow the cell where I'm looking for this formula. I hope it's clear in there the data I'm looking for.

Thanks again in advance!

Try

=SUM(IF(A2=Sheet1!A2:A112,1/(COUNTIFS(Sheet1!$A$2:$A$112,A2,Sheet1!$I$2:$I$112,Sheet1!$I$2:$I$112)),0))


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Thank you John, that worked a treat. Much appreciated.

You're welcome.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Hi John, sorry to ask another question, but whilst this formula is working, in my main spreadsheet it's taking forever to update. I've copied and pasted values for every other area of the spreadsheet to minimise the file size, and ensured data is selected onto from specific cell ranges and not entire columns, but it's still taking forever. I can potentially cut down the calculation sizes and do it in chunks, but before I do that I wondered if there is another version of this formula that would potentially reduce the calculation size/times at all? Many thanks

How many rows?

Hi John,
Sheet 1 has over 70,000 rows which the data is looking up from. Sheet 2 has 16,000 rows where the data needs pulling into.

Try

=SUM(IF(FREQUENCY(IF(Sheet1!$A$2:$A$112=A2,Sheet1!$G$2:$G$112),Sheet1!$G$2:$G$112),1))

Array formula

This returns 17 for your test file which is correct: the COUNTIFS returned 16!

That is perfect, and so much quicker, thanks John!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.