CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulascountif
Omar
Omar

Reputation: 117

Automation Array Formula

I need to make a formula that generates the number of the incident in this data

Whenever someone takes an action from consequences tab I should choose the incident and the number of it so if someone took the same incident before and will take it again this will be the 2nd time with a different action and every action has a 180 days expiry which is represented in the expiry column 0 means expired 1 means, not expired

what I need here is to generate the number of incidents automatically when the array formula looks at the name of the employee then it counts the incident if the incident number is equal 2 then it means 2 times same incident so generate 2nd time and if it is equal 3 it means 3 times same incident ETC...

I have tried this array but it counts all the errors without taking the agent as criteria 

=ARRAYFORMULA(IF(ROW(A:A)=1,"Number of incidents (Automation)",IF(LEN(A:A)=0,IFERROR(1/0),IF(COUNTIF(B:B,B:B)=0," ",IF(COUNTIF(C:C,C:C)=6,"6th Time",IF(COUNTIF(C:C,C:C)=5,"5th Time",IF(COUNTIF(C:C,C:C)=4,"4th Time",IF(COUNTIF(C:C,C:C)=3,"3rd Time",IF(COUNTIF(C:C,C:C)=2,"2nd Time",IF(COUNTIF(C:C,C:C)=1,"1st Time"," "))))))))))

here is a sample of the data https://docs.google.com/spreadsheets/d/1OqxTwyeZlbzYsUYIF6sNqkQS3uzEkpyC15RRVP2P4rA/edit?usp=sharing

Upvotes: 1

Views: 79

Answers (2)

player0
player0

Reputation: 1

try like this in D1:

={"Number of incidents (Automation)"; 
 ARRAYFORMULA(IF(LEN(C2:C), COUNTIFS(C2:C, C2:C, ROW(C2:C), "<="&ROW(C2:C)), ))}

0

if that needs to be per employee then use:

={"Number of incidents (Automation)"; 
 ARRAYFORMULA(IF(LEN(C2:C), COUNTIFS(B2:B&C2:C, B2:B&C2:C, ROW(C2:C), "<="&ROW(C2:C)), ))}

and to exclude expired you can do:

={"Number of incidents (Automation)"; 
 ARRAYFORMULA(IF(F2:F=1, COUNTIFS(B2:B&C2:C&F2:F, B2:B&C2:C&F2:F, ROW(C2:C), "<="&ROW(C2:C)), ))}

0

Upvotes: 1

Hayk Ohanyan
Hayk Ohanyan

Reputation: 31

I didn't get what you exactly want. But hope this helps you.

Instead of

IF(COUNTIF(C:C,C:C)=X,"Xnd Time"

You can write

IF(COUNTIF(C:C,C:C)&"nd Time"

in full formula

=ARRAYFORMULA(IF(ROW(A:A)=1,"Number of incidents (Automation)",IF(LEN(A:A)=0,IFERROR(1/0),IF(COUNTIF(B:B,B:B)=0," ",COUNTIF(C:C,C:C)&"th Time"))))

Upvotes: 0

Related Questions