data meeting criteria but formula somtimes working, then sometimes not

Hello,

I had help on here with a formula
{=COUNT(IF((Incident_Accident!$C$3:$C$987=$C$2)*(Incident_Accident!$L$3:$L$987=$D$2)*(Incident_Accident!$I$3:$I$987='Summary '!$B3),$A$3:$A$33))} The same forumla is used also many columns for Arpil its D,E, F, G, H, I, J. May is L, M, N, O, P,Q,R, and so on for each month. (please see attached spread sheet Summary Tab) This formula is from D3.

For some reason, in the incident / accident tab, when I've input 38 rows of data, matching the criteria to transfer into the Summary Tab, it's not automatically transfering over the matching criteria and inputting into D3, E3, F3, G,3, H3, I3 or J3 if the criteria matches after Row 33. It doesn't seem to like over 31 rows of data. I just can't seem to see why this is doing this.


The forumla below works perfectly bringing in all the criteria, into C3, K3 and so on
=IF(SUMPRODUCT(--(TRIM(Incident_Accident!$I$3:$I$987)=TRIM($B3)),--(TRIM(Incident_Accident!$C$3:$C$987)=TRIM(C$2)))=0,"",SUMPRODUCT(--(TRIM(Incident_Accident!$I$3:$I$987)=TRIM($B3)),--(TRIM(Incident_Accident!$C$3:$C$987)=TRIM(C$2))))

But the first formula just doesn't like it after Row 33 on the Incident / accident Tab.

Can anyone help me on the please, as I can't see what why this is doing this. (I've attached the spread sheet)

Many, Many thanks in advance

probably the last range in your formula =...),$A$3:$A$33)) stops at 33 - Replacing A33 it with a larger value does the job.

OTH you are using lots of array formulas which seems unnecessary and will slow things down

You could use

Please Login or Register  to view this content.

which is much faster