2

I have a large list of clients that have an appointment on 4 different occassions (I call this Subjects): 1st Visit, 1st check up, 2nd check up and final visit. These clients are also linked to a partner: Kate, Dave, Bart, Will, John and Tom.

From this large list of clients I want to be able to extract client names based "Subject" and "Partner" between two dates (for example 1st of January and 1st of March). So I have made the two following dropdown lists:

Subject: No selection, 1st Visit, 1st Check up, 2nd Check up and Final Visit.

Partner: No selection, Kate, Dave, Bart, Will, John and Tom.

So for example if I choose to view all the clients that have an appointment between the 1st of January 2019 and 1st of March 2019, "Subject = 1st Visit" and "Partner = No selection". I want to be able to see all the appointments for the 1st Visit, for all the partners between the above two dates. And if I select the above to dates, "Subject = No selection" and "Partner = No Selection" I want to be able to see all the appointments between the two selected dates for all the subjects and all the partners.

I'm having the following 2 problems in my Excel sheet.

First I'm using the countifs formula (G6 in excel sheet Extract") to count the amount of appointments. But the trouble that I'm having with this is that I cannot seem to correctly write the formula to also count the partners and the "No selection" part. The countifs formula that I have now only counts the subject between two dates, so whenever I select "No selection" for the subject it doesn't give me a count for everything (like I would want it to).

Second My Aggregate formula (G10 in excel sheet "Extract") is a mess, I have to little knowledge to write the formula correctly. I would greatly appreciate any help with this. I think the aggregate function is beautiful, if anyone has got any tips to where I can learn more about this I would also greatly appreciate it.

Link to my excel sheet: https://www.dropbox.com/s/hqntzkj2xq0i250/Aggregate%20function%20struggle.xlsx?dl=0

I know it's the Holidays, perhaps not the best time to ask a question online but these formulas are like a splinter in my mind right now.

  • 1
    Not sure about your second question - would you like to list all Clients that match your filter criteria (i.e. dates, subject, partner)? Taking 1/1/2019-31/12/2019, 1st Visit and Dave as an example, would you like to see a list of 6 client names? – Justyna MK Dec 24 '18 at 22:54
  • Yes exactly. The actual client list is very long and by extracting with the selected criteria I would want to see only what is needed. –  Dec 24 '18 at 23:40
  • Thanks for explaining! I edited my answer (see below). – Justyna MK Dec 25 '18 at 10:50

1 Answers1

0

I hope this will help you with your first question.

In EXTRACTED tab, cell G6, paste the following formula:

=COUNTIFS(INDIRECT("source[[#All],["&$L$5&"]]"),">="&$G$4,INDIRECT("source[[#All],["&$L$5&"]]"),"<="&$N$4,source[[#All],[PARTNER]],IF($J$5="No Selection","<>""",$J$5))

As for your second question, you need to unmerge cells G10:L64 in EXTRACTED tab (as Array formulas are not valid in merged cells). In cell G10, use the following Array formula (confirm with Ctrl+Shift+Enter):

=IFERROR(INDEX(source[[#All],[NAME]],(SMALL(IF((--(INDIRECT("source[[#All],["&$L$5&"]]")>=EXTRACTED!$G$4))*(--(INDIRECT("source[[#All],["&$L$5&"]]")<=EXTRACTED!$N$4))*IF($J$5="No Selection",1,(--(source[[#All],[PARTNER]]=EXTRACTED!$J$5))),ROW(source[[#All],[NAME]]),""),F10))),"")

You can now drag it down to see all results. I know you wanted to use AGGREGATE formula but I followed my solution (sorry!). Once the formula is entered, you can now select cells G10:L10 and click on "Merge & Center".

Hope it helps!

Justyna MK
  • 3,523
  • 3
  • 11
  • 25