Advanced Array Formula Problem

[Magius96]

In the spreadsheet that I'm working on, I'm having to use the following array
formula:

{=SUM(IF(('Data Entry'!$A$2:$A$92='Comparitive Chart'!E$1)*('Data
Entry'!$B$2:$B$92='Comparitive Chart'!$C13),1))}

While this works as desired, the problem is that there may be more than 91
entries on the 'Data Entry' page. If I changed it to look at $A:$A instead
of $A$2:$A$92, then the formula returns an error because of all the unused,
blank cells.

I guess what I need is a way to have the formula dynamically look at only
the cells that actually have values, and ignore the rest. There will be no
empty cells in the middle of the list, they will all be at the bottom of the
spreadsheet.

I initially thought to change the formula to look at a cell that counted the
non-blanks by using counta() by changing the formula to read like this(the
referenced cell is z1)

{=SUM(IF(('Data Entry'!$A$2:address($z$1,1)='Comparitive Chart'!E$1)*('Data
Entry'!$B$2:address($z$1,2)='Comparitive Chart'!$C13),1))}

Of course, this didn't work either.

 

[Harlan Grove]

In the spreadsheet that I'm working on, I'm having to use the
following array formula:

{=SUM(IF(('Data Entry'!$A$2:$A$92='Comparitive Chart'!E$1)
*('Data Entry'!$B$2:$B$92='Comparitive Chart'!$C13),1))}

This could be replaced by the nonarray formula

=SUMPRODUCT(('Data Entry'!$A$2:$A$92='Comparitive Chart'!E$1)
*('Data Entry'!$B$2:$B$92='Comparitive Chart'!$C13))

While this works as desired, the problem is that there may be more
than 91 entries on the 'Data Entry' page. If I changed it to look at
$A:$A instead of $A$2:$A$92, then the formula returns an error
because of all the unused, blank cells.

....

No, it returns an error because Excel 2003 and prior can't handle
arrays that reach 65536 rows. Excel has no trouble whatsoever with
blank cells.

Since you're not using row 1 in the original formula, do you need to
include it in your new formula? If not, try

=SUMPRODUCT(('Data Entry'!$A$2:$A$65536='Comparitive Chart'!E$1)
*('Data Entry'!$B$2:$B$65536='Comparitive Chart'!$C13))

which should work, but it'll be SLOW. While you may need more than 91
rows, would you really need more than, say, 1000 rows? If not, just
use 1000 rows.

If you have widely varying numbers of rows, try a 2-cell approach.
Name the first LastRow and enter the following formula in it.

=LOOKUP(2,1/('Data Entry'!$A$2:$A$65536<>"")
/('Data Entry'!$B$2:$B$65536<>""),ROW('Data Entry'!$B$2:$B$65536)-1)

Then try the formula

=SUMPRODUCT(('Data Entry'!$A$2:
INDEX('Data Entry'!$A$2:$A$65536,LastRow)='Comparitive Chart'!E$1)
*('Data Entry'!$B$2:INDEX('Data Entry'!$B$2:$B$65536,LastRow)
='Comparitive Chart'!$C13))

. . . There will be no empty cells in the middle of the list, they
will all be at the bottom of the spreadsheet.

....

That's a poor design for a spreadsheet.