Array formula problem

M

mark1

I'm entering an array formula in many different cells at
once. Here's my formula (entered as an array):

=SUMPRODUCT(($M7=$J$7:$J$74)*(D$7:D$74)*(IF
($I$7:$I$74="",1,0)))

If I enter this array formula in by highlighting all the
cells that I want this formula in and doing
Ctrl+Shift+Enter, the formula is the same in all cells.
In other words, the cell references aren't relative. They
are all absolute. What I want is to enter this as an
array formula and copy down, so that the next cell down
will have the formula:

=SUMPRODUCT(($M8=$J$7:$J$74)*(D$7:D$74)*(IF
($I$7:$I$74="",1,0)))

M8 instead of M7

Is there a way to copy down an array formula?
 
F

Frank Kabel

Hi
first no need for an array formula. enter the following formula in one
cell:
=SUMPRODUCT(($J$7:$J$74=$M7)*($I$7:$I$74="")*($D$7:$D$74))
or
=SUMPRODUCT(--($J$7:$J$74=$M7),--($I$7:$I$74=""),$D$7:$D$74)

Now copy this formula down
 
M

mark1

If you're out there, Frank:

What is --?

-----Original Message-----
Hi
first no need for an array formula. enter the following formula in one
cell:
=SUMPRODUCT(($J$7:$J$74=$M7)*($I$7:$I$74="")*($D$7:$D$74))
or
=SUMPRODUCT(--($J$7:$J$74=$M7),-- ($I$7:$I$74=""),$D$7:$D$74)

Now copy this formula down


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
F

Frank Kabel

Hi
'--' is a double minus 8unary operator). It coerces the boolean value
to a number (TRUE=1, FALSE=0)
 
Top