Complicated Excel Formula won't autoFill

Factual Questions
1

I have this formula:
{=SUM((REPORT!$A$1:$A$999=$A2)*(REPORT!R$1:R$999=5))}

in an Excel Spreadsheet. When I try use AutoFill to get this thing into the rows
and columns next to it, it only partially works. Sometimes I can pull it across 16 rows, sometimes only three or four. Sometimes I can only pull it down to row 98 and it refuses to go any further–no error message or anything, it just doesn’t work. Further, when I get to row 98, I tried to manually type the formula into row 99 --it vanished as soon as I hit enter. No error, just nothing in the cell any longer. I have other simpler formulas in the cell (like a simple =sum formula), and I can auto fill those to my heart’s content. The only thing different I see is that the one is an arrray formula–why should this affect my ability to use autofill? And how the hell does it decide where to stop? It doesn’t stop at the same spot every time.
To complicate matters—a nearly identical array formula appears elswhere in the spreadsheet (the only difference being the last part says =6 instead of =5), and while I had no problem getting it to fill to every cell I needed, just for fun I tried to see if I could make it go further than I needed–and it won’t anymore.

Anyone know what the heck is going on??

2

Can you explain what this clause means?

(REPORT!$A$1:$A$999=$A2)

I’m not sure I understand what your formula is attempting to do.

I suspect your problem might also have something to do with your absolute ($) cell references.

3

REPORT is another worksheet. The formula is counting with multiple criteria in that spreadsheet
=SUM((REPORT!$A$1:$A$999=$A359)*(REPORT!J$1:J$999=5)
for example is counting all rows where the value in column A is equivalent to the value in A359 of the worksheet where the formula is, AND where that same row has a 5 in column J.

I have tested the formula several ways and as far as I can tell it works just fine…in the cells that the formula is in. My problem is I can’t get the formula into the next cell. At all. Even typing it manually. OK, maybe the next cell or the next few cells, but eventually it decides to stop. I grab the corner of the cell and pull it down to populate the rows beneath it, it gets as far as row 98 and decides it’s not going to auto fill anymore. Normally if something is wrong with the formula, it just gives an error or incorrect information, but it still allows you to put it in the cell–or if there’s a syntax error it gives an error message. I get none of that.

And my simple =sum(a1:a99) type formulas will let me drag them across the whole stinking spreadsheet with no problem, but this array formula won’t. The maddening thing is that each time I try it, it picks a different point to stop.

4

Just guessing - Excel is having some kind of memory management problems. There may be no solution except to simplify your spreadsheet somehow or split it into more than one spreadsheet.

5

I tried your formula in a spreadsheet and while it won’t let me drag it and it won’t let me use “fill” command, it WILL use copy and paste, you can tell it copy, then highlight the column and press paste, and it fills to line #65536, which is as far as you can go. Am I missing something?

6

I was going to suggest using copy and paste as well, but thought that perhaps you may be attempting to increment the last constant (“5”) with the autofill.

When it comes to autofill, I find that Excel isn’t very smart. Especially when the cell contents are formulas. I don’t rely on it except to fill series of numbers, or strings terminating in numbers.

Good luck.

As for your formula, it’s still hard to tell if it’s doing what you want it to do… or what you’re even intending. Are you sure the problem isn’t due to an error in your formula?

Why aren’t you using the COUNT or COUNTIF or DCOUNT functions?

7

Fair enough. Although I agree with bughunter that you should be using a COUNTIF statement.

But this:

is definitely not doing what you think it’s doing. You’re not checking in the same rows at all, just anywhere in column J.

Also, why are you trying to take a SUM of two multiplied items?

I know I’m not necessarily helping you with the fill-down problem, but maybe if you could clarify what you need to do, we could help you find another way to get there.

8

Tested the OP’s formula in Excel and it works just fine. Was also able to drag the formula down the rows and across the columns (as far as can be done) with no problems, i.e., the expected results appeared. Only things I can think of (as someone else has already pointed out) are a memory-related issue, or possibly a defective installation of Excel. Try the formula out on another PC with more memory and another Excel installation and see if the problem still exists. But like I said, it worked just fine for me.

9

thanks for the help everyone…those who suggested a memory issue were correct I believe. I was able to eventually get it to auto fill when I only did a couple rows/columns at a time, I guess I was doing too many for this PC to cope with. Weird thing was that, once I tried to fill it into some columns and failed, it would never let me try again unless I deleted the problematic row.
Whatever.
Like I said, I finally got it to work. it just took a little longer–I had to do it in steps instead of all at once.

As for using Countif—I needed multiple criteria; counting only if the value in column A was x and the value in column C was y, and so on. Is this possible with Countif? The formual I used was the reply I got from an Excel message board when I asked how I could do this, everyone there said Countif wouldn’t work with multiple criteria.

10

Well, it doesn’t, but that’s when you do nested IF statements.


=IF(A=X,IF(B=Y,"True","False"),"False")

The above nested IF formula results in “True” only if A=X and B=Y. Of course, you substitute whatever operations you want instead.

Anyway, that’s how I’d do it.

11

thanks Jpeg–but I don’t think I understand completely. I am trying to get it to count rows where both conditions are met (and the formula I used did work—at least in every test I’ve run so far anyway). So the code you posted tests whether the conditions are met—and returns a true or false, but how exactly do I make it tell me how many times in a given range that the conditions are met?

12

Well, instead of “True” put “1”, and instead of “False” put “”. So, now you see a “1” wherever both conditions are true.

Fill this formula all the way down the column. Then set up a simple =SUM(start:end) formula to tally up the 1s over whatever range you want.

This might be a little inelegant, but it has the advantage of being straightforward, and guaranteed to be accurate.

13

A lot of people really don’t understand array formulas vs conventional formulas in Excel. So I’d take most advice (including mine) with a grain of salt.

Array formulas and autofill are not a happy combo. Use copy and paste instead.

When I encounter a spreadsheet that starts acting wierd, I save a copy as an xml spreadsheet (available only in Excel 2003). Then I retrieve that copy to re-create the XLS from the XML. That often clears glitches that somehow got stored in the innards of the XLS.