Count based on on criteria

[ORoxo]

Hello, everyone
I have three columns, F, AC and E. On column F I have the name of a Risk and on AC I have the department which have identified such risk. I'm trying to give a serial code to risks on colum E. Things to consider:

- Each department can identify the same risk more than once. In such case, the serial code should be the same
- Each time the department changes I would like the serial code to reset the counting (regardless of the name of the risk)

This is what I have so far:

=IF(COUNTIFS(F$2:F2;F2;AC$2:AC2;AC2)=1;MAX(E$1:E1)+1;INDEX($E$2:E2;MATCH(F2;$F$2:F2;0)))

I think I was able to address the first point. However, I can't figure out how to manage the second one.

Could you help me out?
Thanks!

 

[Peter_SSs]

Could we have a small set (say 10-12 rows) of dummy sample data (in columns A & B would do) and the expected results for that sample data (in column C would do)?

 

[ORoxo]

Could we have a small set (say 10-12 rows) of dummy sample data (in columns A & B would do) and the expected results for that sample data (in column C would do)?

Sure, Peter.

This is what I'm looking for:

If you want that sample data:

https://ufile.io/5gfym

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH($A2&"|"&$B2,$A$1:A1&"|"&$B$1:B1,0)),INDEX($C$1:C2,MATCH($A2&"|"&$B2,$A$1:A1&"|"&$B$1:B1,0)),SUM(IF(FREQUENCY(IF(1-($A$2:A2=""),IF($B$2:B2=B2,MATCH($A$2:A2,$A$2:A2,0))),ROW($A$2:$B2)-ROW(INDEX($A$2:$B2,1,1))+1),1)))

 

[ORoxo]

In C2 control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH($A2&"|"&$B2,$A$1:A1&"|"&$B$1:B1,0)),INDEX($C$1:C2,MATCH($A2&"|"&$B2,$A$1:A1&"|"&$B$1:B1,0)),SUM(IF(FREQUENCY(IF(1-($A$2:A2=""),IF($B$2:B2=B2,MATCH($A$2:A2,$A$2:A2,0))),ROW($A$2:$B2)-ROW(INDEX($A$2:$B2,1,1))+1),1)))

Ok, this some wizardry...but it worked perfectly though.

Can you please explain what does in practice using &"|"&?

Thanks!

 

[Peter_SSs]

If you have MAXIFS and MINIFS, you could use this standard-entry formula in C2, copied down.
=IF(COUNTIF(B$2:B2,B2)=1,1,IF(COUNTIFS(A$1:A1,A2,B$1:B1,B2),MINIFS(C$1:C1,A$1:A1,A2,B$1:B1,B2),MAXIFS(C$1:C1,B$1:B1,B2)+1))

Otherwise you could use this (also standard-entry)
=IF(COUNTIF(B$2:B2,B2)=1,1,IF(COUNTIFS(A$1:A1,A2,B$1:B1,B2),INDEX(C$1:C1,MATCH(A2&"|"&B2,INDEX(A$1:A1&"|"&B$1:B1,0),0)),AGGREGATE(14,6,C$1:C1/(B$1:B1=B2),1)+1))

 

[Aladin Akyurek]

Ok, this some wizardry...but it worked perfectly though.

Can you please explain what does in practice using &"|"&?

Thanks!

You are welcome. The | symbol is used as preacaution against undesired results which might occur due to concatenation like in A and 01 vs A0 and 1. Both would end up as A01 instead of A|01 and A0|1.

 

[ORoxo]

If you have MAXIFS and MINIFS, you could use this standard-entry formula in C2, copied down.
=IF(COUNTIF(B$2:B2,B2)=1,1,IF(COUNTIFS(A$1:A1,A2,B$1:B1,B2),MINIFS(C$1:C1,A$1:A1,A2,B$1:B1,B2),MAXIFS(C$1:C1,B$1:B1,B2)+1))

Otherwise you could use this (also standard-entry)
=IF(COUNTIF(B$2:B2,B2)=1,1,IF(COUNTIFS(A$1:A1,A2,B$1:B1,B2),INDEX(C$1:C1,MATCH(A2&"|"&B2,INDEX(A$1:A1&"|"&B$1:B1,0),0)),AGGREGATE(14,6,C$1:C1/(B$1:B1=B2),1)+1))

It also worked, Peter. Thanks!

 

[Peter_SSs]

It also worked, Peter. Thanks!

No problem. Glad to help.