SUM IF problem

[ttmannan]

I have the following formula
=SUM(IF(SCAR!$A$8:$A$9000=Sheet3!H$1,IF(SCAR!$H$8:$H$9000=Sheet3!$F44,1,0)))

And it seemed to work pretty well so I dragged it into 4 additional
columns and 74 additional rows (allowing the non $ protected variables
to change) and it worked on a few but then returns zeros everywhere
else. It should continue to count the occurrances of what is input in
the "F and H" but it just seems like Excel gave up after 25 stores and
left the other 49 hanging.... see example below.... it picked up at the
end but I wonder about that too because the store number (far left
column (F column)) at the bottom are low, double digit numbers. The
TOTAL on 178 is 30 and it should be spread between the 5 columns
indicating to me what makes up the total.... Any help would be
greatfully appreciated. Thanks in advance!!!!!

TOTAL LATE MFRB/C NO PMK ONHND+ OTHER
1 61 5 20 0 9 27
4 58 11 13 3 8 23
5 155 25 24 13 22 71
8 95 9 14 8 17 47
11 197 28 29 28 21 91
12 138 18 29 7 21 63
13 124 16 22 7 20 59
15 19 2 0 3 9 5
25 29 5 5 0 5 14
102 37 3 11 0 3 20
108 49 4 11 3 6 25
111 30 4 9 3 4 10
161 17 0 1 1 2 13
167 24 4 8 1 4 7
178 30 0 0 0 0 0

 

[Max]

Not sure whether this would help with the recalc / recalc efficiency issues,
but guess we could try the equivalent non-array SUMPRODUCT:

=SUMPRODUCT((SCAR!$A$8:$A$9000=Sheet3!H$1)*(SCAR!$H$8:$H$9000=Sheet3!$F44))