Count If and a ton of conditions

Hi All! I joined this forum specifically because of this problem, so I'm hoping someone can help.

I'm using Microsoft Excel 2016 on Mac.

I have a multi-sheet spreadsheet with dates (Column A), Patient numbers (Column B), and Patient last name (Column C). I am in charge of pulling reports from this spreadsheet every month to count how many unique patients were seen. Most patients are seen multiple times a month. This usually requires me to sort the list and hand count them (I know I know.. so anti-Excel). I want to automate this report so I can type in a date range (ie: 8/1/18 (cell B1) and 8/31/18 (cell C1) ) and it automatically counts the unique visits in that range. Is this possible? I've done do much research and can't seem to find what I'm looking for. Ideas?

Thank you!

Tracy

What I normally do to count unique values in a Pivottable. Then you can also use eighter a timeline or slicer to select a period

All you need on top of the pivottable is a cell with a COUNTA formula that starts below Headerrow select a long range so that no patients will be missed due to short range so set for example F3:F500 as range. Also be aware of a total row as that will be counted as 1 argument to.

It will help a lot if you have an example file fake patients names to show you how. Instead of an explanation only

Originally Posted by TMS

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I believe I attached a very simplified version.

Originally Posted by FlameRetired

This non array entered also works.

Formula:

Please Login or Register  to view this content.

I attempted to use this formula because I wasn't sure about the array-entered formula. It is giving me a DIV/0 error. I had to change some of the details because the name of the second page (2018 in the one I send) is different and the data expands from A7:A3000. So I replaced and this is the formula now: =SUMPRODUCT((('2018 For Luella'!A7:A3000>=B1)*('2018 For Luella'!A7:A3000<=C1))/COUNTIFS('2018 For Luella'!B7:B3000,'2018 For Luella'!B7:B3000,'2018 For Luella'!A7:A3000,">="&B1,'2018 For Luella'!A7:A3000,"<="&C1)). I can sort of follow this formula with the countifs but am not familiar with the sumproduct portion.

Thank you for this idea. I am attempting to pass this task on to some who is very unfamiliar with Excel, so I think a Pivottable would be too complex. I should have mentioned that in my original post!