Anyone can optimize these lookup / sumifs ?

[JumboCactuar]

Hi,

i have the following:

Name	Pass Rate	Previous Score	Accumulative Points	Accumulative minus Reduction	Result	Value
John	100.00%	#DIV/0!	89	89	PASSED	89
Luke	0.00%	0	0	0	FAILED	44
George	100.00%	0	82	82	PASSED	82
Tim	100.00%	0	95	95	PASSED	95
Luke	0.00%	44	0	0	FAILED	63
Fred	100.00%	0	91	91	PASSED	91
Lisa	100.00%	0	88	88	PASSED	88
Harry	100.00%	0	81	81	PASSED	81
Paul	100.00%	0	89	89	PASSED	89
George	100.00%	82	177	167	PASSED	95
John	50.00%	89	89	146	FAILED	67
Fred	50.00%	91	91	156	FAILED	75
Bob	100.00%	0	82	82	PASSED	82
Paul	100.00%	89	182	172	PASSED	93
Tim	100.00%	95	185	175	PASSED	90
George	66.67%	95	177	244	FAILED	77
Paul	100.00%	93	268	258	PASSED	86
Fred	66.67%	75	180	245	PASSED	89
Luke	0.00%	63	0	0	FAILED	44

<tbody>
</tbody>

Columns B,C,D and E are all "running values"
Problem i have is when im working with a lot of data (5k rows+), when i input a new value in column A - excel hangs and is slow at calculating. Im guessing because are rely on A values.

Here are the formulas dragged down:

A2 - Input Data

B2

=COUNTIFS($A$2:$A2,A2,$F$2:$F2,"PASSED")/COUNTIF($A$2:$A2,A2)

C2

=IF(A2="",0,IF(ISNA(LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)),0,LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)))

D2

=IF(A2="",0,SUMIFS(G$2:G2,A$2:A2,A2,F$2:F2,"PASSED"))

E2

=IF(D2=0,0,IF(SUMIF(A$2:A2,A2,G$2:G2)>100,SUMIF(A$2:A2,A2,G$2:G2)-10,SUMIF(A$2:A2,A2,G$2:G2)))

F2

=IF(G2>80,"PASSED","FAILED")

G2 - Input Data


I think the problem lies mainly with the formulas in C, like i dont know if i can check for NA a better way

Appreciate any help

 

[JumboCactuar]

For column C, am I better using something like =IFERROR(anything,0)

 

[Marcelo Branco]

Maybe...

C2 copied down
=IF(A2="",0,IF(ISNA(MATCH(A2,A$1:A1,0)),0,LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)))

M.

 

[JumboCactuar]

Maybe...

C2 copied down
=IF(A2="",0,IF(ISNA(MATCH(A2,A$1:A1,0)),0,LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)))

M.

thankyou i will try it

anything for column E? Am i better doing the SUM IF then the deduction in a seperate column?

 

[JumboCactuar]

Bump