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) 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) 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.
Aucun commentaire:
Enregistrer un commentaire