Hi Community,
This is my first post, I hope you will be able to help me
I am trying to do 2 counts. Firstly, a count of the number of devices that are being used in each ward of the hospital.
Secondly, a count of the number of patients in each ward in the hospital.
I have attached sample data with the manually calculated results.
Many thanks
Leeroy
In G4 copied down:
=SUMPRODUCT((1/(COUNTIF($C$4:$C$11,$C$4:$C$11))*($B$4:$B$11=F4)))
G5 in your WB is inaccurate.
In H4 copied down:
=COUNTIF($B$4:$B$11,F4)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.
Forum Rules (updated September 2018): please read them here.
How to use the Power Query code you've been given: help here. More about the Power suite here.
You could also add the table to the data model and do a pivot using distinct count for the patient.
Rory
Thanks AliGW . Those formulas worked. However, when trying to adjust the formula so that it looks at all of column B & C (except B1:C3) i get back #DIV/0! I tried to change the formula to this
=SUMPRODUCT((1/(COUNTIF($C$4:$C$1048576,$C$4:$C$1048576))*($B$4:$B$1048576=K4)))
Any ideas?
I imagine the real data has blank rows.
Please provide a more realistic sample dataset.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
More Information
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks