Array Formula Required

[abid.aries]

Dear Experts,

Hope you all doing great and had a great week.

My problem is that, This is my formula based on AND & countifs.

Below formula is calculating Late "L" and when it is true it return 0.

=IF(AND(R281="L",OR(COUNTIFS($A$2:A281,A281,$Q$2:Q281,"L")=3,COUNTIFS($A$2:A281,A281,$Q$2:Q281,"L")=6,COUNTIFS($A$2:A281,A281,$Q$2:Q281,"L")=9,COUNTIFS($A$2:A281,A281,$Q$2:Q281,"L")=12,COUNTIFS($A$2:A281,A281,$Q$2:Q281,"L")=15,COUNTIFS($A$2:A281,A281,$Q$2:Q281,"L")=18,COUNTIFS($A$2:A281,A281,$Q$2:Q281,"L")=21,COUNTIFS($A$2:A281,A281,$Q$2:Q281,"L")=24)),0)

Problem is that it is calculating all the data and take too much time and processing speed and ultimately slow down my working.

I need an array formula that eliminate time and processing consumption.

Thanks in advance.

 

[chirayu]

Please attach sample file

 

[abid.aries]

Dear Chairaya,

Please find attached sample sheet.

 

[chirayu]

try this

=IFERROR(IF(AND(L2="L",COUNTIFS($A$2:$A2,A2,$K$2:$K2,"L")=COUNTIFS($A$2:$A2,A2,$K$2:$K2,"L")={3,6,9,12,15,18,21,24,27,30}),0,VLOOKUP(L2,Parameter!$A:$B,2,0)),0)

 

[abid.aries]

No on every third Late I need a zero.

 

[chirayu]

then your original formula doesn't really make sense as what its doing is

1) Check if "Modified Attend. Status" is L
2) Check if cells in "System Status" up to current cell are also "L" and there are either 3,6,9,12,15,18,21,24,27,30 L's
3) Basis that do 0 against any L in "Count Attendance"
4) If none of the above criteria match then just do the VLOOKUP
5) Otherwise 0

So it doesn't actually take into account every 3rd L = 0

Can you please list out a set of rules for the same as to how it should calculate

 

[bosco_yip]

Based on your formula, in M2 modified formula copied down :

=IF(AND(L2="L",(COUNTIFS($A$2:A2,A2,$K$2:K2,"L")<25)*(MOD(COUNTIFS($A$2:A2,A2,$K$2:K2,"L"),3)=0)),0,IF(ISNUMBER(MATCH(L2,Parameter!$A$2:$A$14,0)),VLOOKUP(L2,Parameter!$A$2:$B$14,2,0),0))

Regards
Bosco

 

[abid.aries]

Thanks Bosco it worked

 

[abid.aries]

then your original formula doesn't really make sense as what its doing is

1) Check if "Modified Attend. Status" is L
2) Check if cells in "System Status" up to current cell are also "L" and there are either 3,6,9,12,15,18,21,24,27,30 L's
3) Basis that do 0 against any L in "Count Attendance"
4) If none of the above criteria match then just do the VLOOKUP
5) Otherwise 0

So it doesn't actually take into account every 3rd L = 0

Can you please list out a set of rules for the same as to how it should calculate

Checked every thing but not working.

 

[bosco_yip]

Thanks Bosco it worked

Or, try this shorter formula instead,

=IF(AND(L2="L",MOD(MIN(25,COUNTIFS($A$2:A2,A2,$K$2:K2,"L")),3)=0),0,IFERROR(VLOOKUP(L2,Parameter!$A$2:$B$14,2,0),0))

Regards
Bosco

 

[abid.aries]

Great its working and Sorry for late reply.