Hello,

I am trying to determine a sumproduct for two ranges but only for rows
where data is not present in a third range

For instance

The range a2:A5 has the following values
2
1
1
1

The range C2:C5 has the values
5
10
15
18
The range B2:B5 are empty except cell B3

I want to write a formula that will give me the sum product of A2&C2,
A4&C4, A5&C5

Cells in range B2:B5 may vary if empty or not
The A & C ranges will be of variable length and values

Any help is appreciated

Glen

Hi!

Try this:

=SUMPRODUCT(--(B2:B5=""),A2:A5,C2:C5)

Biff

"gmunro" wrote in message
ps.com...
Hello,

I am trying to determine a sumproduct for two ranges but only for rows
where data is not present in a third range

For instance

The range a2:A5 has the following values
2
1
1
1

The range C2:C5 has the values
5
10
15
18
The range B2:B5 are empty except cell B3

I want to write a formula that will give me the sum product of A2&C2,
A4&C4, A5&C5

Cells in range B2:B5 may vary if empty or not
The A & C ranges will be of variable length and values

Any help is appreciated

Glen

this worked for me:
=SUM(IF(B2:B5<"",A2:A5*B2:B5*C2:C5,0))

Enter as an array formula, ctrl-shift-enter



gmunro wrote:
Hello,

I am trying to determine a sumproduct for two ranges but only for rows
where data is not present in a third range

For instance

The range a2:A5 has the following values
2
1
1
1

The range C2:C5 has the values
5
10
15
18
The range B2:B5 are empty except cell B3

I want to write a formula that will give me the sum product of A2&C2,
A4&C4, A5&C5

Cells in range B2:B5 may vary if empty or not
The A & C ranges will be of variable length and values

Any help is appreciated

Glen

You can use sumproduct this way:
=SUMPRODUCT(A2:A5,C2:C5,--(B2:B5=""))
Change the ranges as appropiate

Hope this helps,
Miguel.

"gmunro" wrote:

Hello,

I am trying to determine a sumproduct for two ranges but only for rows
where data is not present in a third range

For instance

The range a2:A5 has the following values
2
1
1
1

The range C2:C5 has the values
5
10
15
18
The range B2:B5 are empty except cell B3

I want to write a formula that will give me the sum product of A2&C2,
A4&C4, A5&C5

Cells in range B2:B5 may vary if empty or not
The A & C ranges will be of variable length and values

Any help is appreciated

Glen

Biff wrote:
Hi!

Try this:

=SUMPRODUCT(--(B2:B5=""),A2:A5,C2:C5)

Biff

This worked perfectly. You guys are the wind beneath my wings.
Incidentally, what does the -- mean"

Glen

This worked perfectly thank you.
What does the "--" mean?
Can it be used in other functions?

Glen

Biff wrote:
Hi!

Try this:

=SUMPRODUCT(--(B2:B5=""),A2:A5,C2:C5)

Biff

"gmunro" wrote in message
ps.com...
Hello,

I am trying to determine a sumproduct for two ranges but only for rows
where data is not present in a third range

For instance

The range a2:A5 has the following values
2
1
1
1

The range C2:C5 has the values
5
10
15
18
The range B2:B5 are empty except cell B3

I want to write a formula that will give me the sum product of A2&C2,
A4&C4, A5&C5

Cells in range B2:B5 may vary if empty or not
The A & C ranges will be of variable length and values

Any help is appreciated

Glen

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

And if you did:

Say A1 = 1234
=right(a1,1)
you'd get text back ("4")
=--right(a1,1)
would return 4 (as a number)

gmunro wrote:

This worked perfectly thank you.
What does the "--" mean?
Can it be used in other functions?

Glen

Biff wrote:
Hi!

Try this:

=SUMPRODUCT(--(B2:B5=""),A2:A5,C2:C5)

Biff

"gmunro" wrote in message
ps.com...
Hello,

I am trying to determine a sumproduct for two ranges but only for rows
where data is not present in a third range

For instance

The range a2:A5 has the following values
2
1
1
1

The range C2:C5 has the values
5
10
15
18
The range B2:B5 are empty except cell B3

I want to write a formula that will give me the sum product of A2&C2,
A4&C4, A5&C5

Cells in range B2:B5 may vary if empty or not
The A & C ranges will be of variable length and values

Any help is appreciated

Glen


--

Dave Peterson