Hi Rick,

Well surprisingly, and maybe because of particular conditions on my sheet,
Topper's and yours both return the same output. I tried the other one you
suggest and it works too. I will change the values to study their reactions.

What I definitely need the formula to do is multiply every odd row in D2:D63
* every even row in B2:B63, then add the results. I think it is almost the
same as what you stated earlier:

assuming the match up for multiplication is the odd row from the "D"
column
times the previous row in the "B" column.

To clarify, I need: =D3*B2+D5*B4+D7*B6+....

Thanks.

RF

"Rick Rothstein (MVP - VB)" wrote:

Wait a minute... I am not so sure it does what you want anymore.

You said earlier that Toppers' solution worked for you. My formula does not
produce the same results as his. Which one produces the correct result for
you... Toppers' or mine? I can produce the same result as Toppers' formula
with a slight modification to the one I posted earlier...

=SUMPRODUCT((B2:B62)*(D3:D63)*MOD(ROW(D3:D63),2))

What it does (and what Topper's does)(and perhaps what you asked for when
you said "non-consecutive") is multiply only the odd rows in "D" by the even
rows in "B". That is decidedly different than what my original formula did,
which was to multiply EVERY row's value in "D" by the previous row's value
in "B".

I am not entirely sure what you actually were after, but you should study
the results from my two formulas, and Toppers' formula, to make sure you are
getting the result you really want.

Rick


This one works as well... I don't know why did I try building such a long
formula if a simple one like this does the job. As one of my orevious
teachers would say: "it's the elegant solution".

Thanks Rick.

Regards,

RF

"Rick Rothstein (MVP - VB)" wrote:

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows
from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0)))
but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the
"real"
progress on odd rows and "planned" progress on even rows.

Does this do what you are looking for?

=SUMPRODUCT((B2:B62)*(D3:D63))

assuming the match up for multiplication is the odd row from the "D"
column
times the previous row in the "B" column.

Rick