Tracker - Multiple Actions being tracked, need to exclude duplicates

[excelgal2016]

Hi all,

I am extremely bad at verbally explaining things, so I hope I'll manage to explain what I need clearly.

I have a "case tracker", where I track actions on cases and their status. There are several actions in the process (5+). Below is a very simplified example:

So basically, in this tracker I need to capture actions historically by date. That means that for each action, there will have to be new line entry (this can't be changed, it has to do with complexity of the tracker). On top of the historical data, I need to add "Open Cases" stats as per the above.

However, I need to be able to identify duplicates (by ID #) and if the second (third, fourth, ...) action ="Closed", this ID # and all the entries for that ID have to be excluded from the count of open cases. As per the above, now it counts as 3 open cases, whereas there is only 1 (#1 Closed, #2 Closed, #3 Submitted). I can't figure out how to do this and any help would be very appreciated!

Thanks

 

[AlphaFrog]

Try this. Confirm with Ctrl+Shift+Enter

=SUM(IF(FREQUENCY(B2:B8,B2:B8),1))
-SUM(--(FREQUENCY(IF(C2:C8="Closed",B2:B8),B2:B8)>0))


Assumes the ID# are numeric

 

[excelgal2016]

Try this. Confirm with Ctrl+Shift+Enter

=SUM(IF(FREQUENCY(B2:B8,B2:B8),1))
-SUM(--(FREQUENCY(IF(C2:C8="Closed",B2:B8),B2:B8)>0))


Assumes the ID# are numeric

Worked as a charm! Thanks a lot, AlphaFrog :D )

 

[excelgal2016]

May I have one more question please - is it possible to add more text options apart from the "Closed"? And if any of them is listed, the case is excluded as well?

Thanks

 

[AlphaFrog]

May I have one more question please - is it possible to add more text options apart from the "Closed"? And if any of them is listed, the case is excluded as well?

Thanks

Change the text options to suit.
Confirm with Ctrl+Shift+Enter

=SUM(IF(FREQUENCY(B2:B8,B2:B8),1))
-SUM(--(FREQUENCY(IF(C2:C8={"Closed","Closed2","Closed3"},B2:B8),B2:B8)>0))

 

[excelgal2016]

change the text options to suit.
Confirm with ctrl+shift+enter

=sum(if(frequency(b2:b8,b2:b8),1))
-sum(--(frequency(if(c2:c8={"closed","closed2","closed3"},b2:b8),b2:b8)>0))

thank you so much!

 

[excelgal2016]

Hi AlphaFrog - I'm afraid it's me again

I am struggling with something else in very similar terms. Now I need to count the below:

I need to count:

How many unique online enquiries did we have from US?

or

How many "UNIQUE" (col B) "ONLINE" (col A) "US" (col C) enquiries did we have?

Sorry for being a pain! :D

I would be very grateful for another great advice.

 

[AlphaFrog]

Lots of forum volunteers could answer this type of question. Just start a new thread.

Try this. It presumes the IDs are numeric.
=SUM(IF(FREQUENCY(B2:B6*(A2:A6="Online")*(C2:C6="US"),B2:B6),1))