SUM IF problem

T

ttmannan

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

Roger Govier

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


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

Max Wrote:
Click to expand...
 
M

Max

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
 
Top