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

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))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ttmannan" wrote in message
...

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


--
ttmannan

Max, I wish it would have worked, but unfortunately it didn't. Thanks for your suggestion!

Hi

Max's solution should have worked. As an alternative (still using
SUMPRODCUT) you could try

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

You mention your other formula missing stores. It sounds as though some of the Store numbers are NUMBERS and some are TEXT.
In another column away from your data enter the formula =F44+1 and copy down. If you get errors, then those values are text not numeric. Get them all to the same format, and ensure that the comparison cell is of the same format, and you should be OK.


Regards

Roger Govier



ttmannan wrote:

Max, I wish it would have worked, but unfortunately it didn't. Thanks
for your suggestion!

Max Wrote:


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))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ttmannan" wrote in message
...-

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


--
ttmannan-

Sorry to hear it didn't work, hope that Roger's response fared better for
you

Maybe also see: http://www.decisionmodels.com/calcsecrets.htm
(Try CTRL+ALT+F9 to force full calculations ?)

And/or, take it out and try it in a new file with just the sheet: SCAR and
your sheet with the 75R x 15C formulas* in it (your current file probably
contains a few volcanic-loads of calcs for Excel to do <g)
*Use either your original array formula (which works), or the 2 alternative
SUMPRODUCT versions suggested
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ttmannan" wrote in message
...

Max, I wish it would have worked, but unfortunately it didn't. Thanks
for your suggestion!