help analysing huge sheet

Hello all,
I need your help analysing these data if possible.
This is the number of medications charged on patients in a data range, and arranged per patient or "MR"

Is it possible to know how many total days the patient was charged? like for example MR1 charged first on 16/10/2017 and last charging on 19/10/2017
Meaning number of days is 4.

is there an easy way to do all the MRs without checking one by one?

Also... can it be further analysed by item name afterwards?

Thanks

Microsoft Excel - Oct-2017.xls [Compatibility Mode]_2018-01-16_10-18-03.jpg

ps. i couldn't find a way to attach or post part of the sheet itself so i took this screenshot, please tell me if th forums has a a feature to insert tables?

Thanks again

Do you know how to use pivot tables?

Attach an example file and then I could have a go.

Thanks,
I just uploaded a sample file

See if this does what you want.

Thanks for your superfast help,
it does count, but i need to know the interval between the dates.
for example. "Mr2 was issued between 26/10/2017 and 30/10/2017" meaning 5 days. but the pivot is counting what's listed in the data
.. can it calculate the different between the first day and the last?

There's probably a better way than this but here goes.

See attached

N5

=MAX(IF($F$2:$F$83=$K5,$C$2:$C$83))

O5

=MIN(IF($F$2:$F$83=$K5,$C$2:$C$83))

Both array entered ..

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

P5

=N5-O5+1

Obviously you can combine formulae into one

=MAX(IF($F$2:$F$83=$K5,$C$2:$C$83))-MIN(IF($F$2:$F$83=$K5,$C$2:$C$83))+1

Array entered
.

Same as John, just adds the result as part of the pivot table so it's dynamic.

I would go for the Pivot table!

Thank you guy very much, this will help me a lot. appreciate your input.

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