CODEWITHSUNDEEP
excelexcel-formula
Sam
Sam

Reputation: 746

COUNTIFS when counting distinct values

I've got a table that looks something like this

enter image description here

what I want to do is have a formula that counts how many distinct machines are used on each hospital in between 04/06/2021 and 07/06/2021

So I would get something like this

enter image description here

I feel like this could be done with a COUNTIFS but somehow make it count only distinct values. I'm just not sure of how I could do this. Can someone help me with this? Thanks

Upvotes: 0

Views: 856

Answers (1)

EDS
EDS

Reputation: 2195

To expand on the comments, if you have the newest version of Excel you can use functions like FILTER, COUNTA, and UNIQUE.

For example, after getting the unique hospitals with UNIQUE, try something like:

=COUNTA(UNIQUE(
FILTER($B$2:$B$10,
(($C$2:$C$10<DATEVALUE("1/5/2021"))*
($C$2:$C$10>=DATEVALUE("1/1/2021"))*
($A$2:$A$10=$F2)))))

enter image description here

Upvotes: 2

Related Questions