Analysing data from multiple row records

[burnsbugs]

H,
I'm hoping some of you can give me ideas on how to deal with our challenge. We are developing a system that will accept excel data files on counseling and health services accessed by users. The users may get the same or different services from multiple agencies in different locations. We want to analyse the combined data to report on the cascade of service accessed and the mobility of users among the locations. This initial phase is meant to show what can be reported from the data and support discussions with the users for the final system design. The final system will most likely be programmed in SQL 2012, but we are using Excel and Access for the initial phase.
The data looks like this, but the real files contain many more services:
ID Age Sex Date Service Cinic
XX123 30 M 10/03/2016 TEST North
XX123 30 M 15/03/2016 ART North
XX123 30 M 25/03/2016 ART South
XX111 21 F 3/03/2016 TEST East
XY222 25 F 5/03/2016 TEST West
XY222 25 F 20/03/2016 TEST Upper
ZZ321 42 M 22/03/2016 TEST Lower
ZZ333 38 F 20/03/2016 ART North
ZZ333 38 F 28/03/2016 ART South

Is there a way of using functions such as COUNTIFS, SUMIFS, etc without having to merge the rows into a single row per ID? Do I need to use pivot tables, macros or move the data into Access? Thanks for any ideas!

[Rabbit]

I don't know what you mean my using count if without merging rows. What would the results be of the sample data above?

[burnsbugs]

Hi Rabbit. Thanks for replying. My initial thought was to combine the rows so there would be one record per person and then proceed with the counts of who accessed what service and where. But, I'm trying to see if there is a way to analyse the data from the multiple rows, as a friend thinks that would be more flexible.
For example, we want to create reports showing the number of people who have accessed a certain mix of services (choices include counselling, condom pickups, medical referrals, tests and treatment) and the number that have moved between the various clinics.

[Rabbit]

Yes, you can analyze multiple rows. And you should, that's the preferred method. Denormalizing the data and bringing all onto one row is usually a bad idea.

Again, your descriptions are very vaguely defined. All I can say is you can and should analyze it over multiple rows. And you will probably use an aggregate query of some sort. How you go about doing so will depend on your exact requirements which you have not specified.

[zmbd]

see if this tutorial helps...
MS: COUNTIFS function

If not, then there are other methods such as the DCOUNT()