Urgent - Array Formula

  • Thread starter Thread starter Dee
  • Start date Start date
D

Dee

Hi,

Here is the scenario:

Column A contains quantities - every second one is
formatted with a red font colour.

Column C contains prices - every second one is formatted
with a red font colour

I would like one result cell to contain an array formula
that muliplies all red entries in Col A with all red
entries in Col C. Incidentally, each red quantity has a
red price on the same line.

The second result cell would contain the same as above,
only showing the black (or every second) entries.

It works fine when I either refer to the named cells or
simply drag over a consecutive range. However, the
minute I select non-consecutive cells either manually or
through a named group of cells, I get a Value error.

HELP!

Thanks!
 
Hi
what formula are you using which resulted in this error (or what macro
function?)
 
here is the formula:

=SUM(($B$4,$B$6,$B$8,$B$10)*(G4,G6,G8,G10))

the curly brackets don't show here but are placed at the
very beginning and very end as always with an array.

thanks
 
It worked. thank you so much.

Can you explain in plain language what the formula is
doing?

Thanks again.
 
Hi
SUMPRODUCT multiplies and sums the arrays (see Excel's help). In this
case there're 3 arrays:
1. $B$4:$B$10
2. $G$4:$G$10
3. --(MOD(ROW($B$4:$B$10),2)=0))

The first two are simple. Without the 3rd element SUMPRODUCT would
calculate B4*G4+B5*G5+...B10*G10

But as you want only every second row the third element is used. The
part:
MOD(ROW($B$4:$B$10),2)
returns the remainder of rhe row number divided by 2. So it returns
B4: 0
B5: 1
B6: 0
....
B10:0

Now this result is compared to '=0'
So this part returns after this comparison:
B4: TRUE
B5: FALSE
B6: TRUE
....
B10:TRUE

Now the unary operator (double minus) is used to coerce the boolean
value to a real number (TRUE=1/FALSE=0). So the element in total
returns
B4: 1
B5: 0
B6: 1
....
B10:1

Used in SUMPRODUCT this results to the calculation of:
=B4*G4*1+B5*G5*0+...B10*G10*1

So only every second (all even row numbers) row are counted
 
I am not sure how you learned all of this - it's amazing.

Thanks for the breakdown of the formula. I've also
tested it on odd rows and it works perfectly.

Is there any way to tell it to only sumproduct the arrays
that are formatted with red font colour?

Thanks for everything!
 
Back
Top