trying to do a formula along the lines of: search column b for "n", if its an
"n" then multilply column d by column c. I need it to include all occurances
though. so in the exapmle below it would include eeee, hhhh, and kkkk.
does anyone know what a proper formula would be?
many thanks.

A b c d
eeee n 60 1
ffff y 55 2
gggg j 12 1
hhhh n 50 2
iiii j 14 3
jjjj y 14 1
kkkk n 50 1

=SUMPRODUCT(--(B1:B100="n"),C1:C100,D1:D100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"CMAC" wrote in message
...
trying to do a formula along the lines of: search column b for "n", if its
an
"n" then multilply column d by column c. I need it to include all
occurances
though. so in the exapmle below it would include eeee, hhhh, and kkkk.
does anyone know what a proper formula would be?
many thanks.

A b c d
eeee n 60 1
ffff y 55 2
gggg j 12 1
hhhh n 50 2
iiii j 14 3
jjjj y 14 1
kkkk n 50 1

One way

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))


Regards

Peo Sjoblom


"CMAC" wrote:

trying to do a formula along the lines of: search column b for "n", if its an
"n" then multilply column d by column c. I need it to include all occurances
though. so in the exapmle below it would include eeee, hhhh, and kkkk.
does anyone know what a proper formula would be?
many thanks.

A b c d
eeee n 60 1
ffff y 55 2
gggg j 12 1
hhhh n 50 2
iiii j 14 3
jjjj y 14 1
kkkk n 50 1

Just curious. Why would you use

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))

instead of

=SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8))

Is there a speed issue? Or am I missing something else?

Thanks,
Tim C

"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))


Regards

Peo Sjoblom


"CMAC" wrote:

trying to do a formula along the lines of: search column b for "n", if
its an
"n" then multilply column d by column c. I need it to include all
occurances
though. so in the exapmle below it would include eeee, hhhh, and kkkk.
does anyone know what a proper formula would be?
many thanks.

A b c d
eeee n 60 1
ffff y 55 2
gggg j 12 1
hhhh n 50 2
iiii j 14 3
jjjj y 14 1
kkkk n 50 1

I wasn't consistent, Bob's formula was

http://www.mcgimpsey.com/excel/formulae/doubleneg.html



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Tim C" wrote in message
...
Just curious. Why would you use

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))

instead of

=SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8))

Is there a speed issue? Or am I missing something else?

Thanks,
Tim C

"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))


Regards

Peo Sjoblom


"CMAC" wrote:

trying to do a formula along the lines of: search column b for "n", if
its an
"n" then multilply column d by column c. I need it to include all
occurances
though. so in the exapmle below it would include eeee, hhhh, and kkkk.
does anyone know what a proper formula would be?
many thanks.

A b c d
eeee n 60 1
ffff y 55 2
gggg j 12 1
hhhh n 50 2
iiii j 14 3
jjjj y 14 1
kkkk n 50 1

I multiply the terms "manually" instead of separating them with a comma,
thereby forcing the conversion without the double negative. I end up with a
single term, with SUMPRODUCT taking care of the adding and the array
handling. To me this results in a cleaner, more easily understood formula.

Is this just a personal preference or are there advantages to doing it Bob's
way?

(My formula would be more concise if I took out the last set of parenthesis,
but I think the intent of the formula is more easily understood with them in
place. This can be very helpful for later troubleshooting or modification.)

Tim C

"Peo Sjoblom" wrote in message
...
I wasn't consistent, Bob's formula was

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

"Tim C" wrote in message
...
Just curious. Why would you use

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))

instead of

=SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8))

Is there a speed issue? Or am I missing something else?

Thanks,
Tim C

"Peo Sjoblom" wrote in message
...
One way

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))

Regards

Peo Sjoblom


"CMAC" wrote:

trying to do a formula along the lines of: search column b for "n", if
its an
"n" then multilply column d by column c. I need it to include all
occurances
though. so in the exapmle below it would include eeee, hhhh, and kkkk.
does anyone know what a proper formula would be?
many thanks.

A b c d
eeee n 60 1
ffff y 55 2
gggg j 12 1
hhhh n 50 2
iiii j 14 3
jjjj y 14 1
kkkk n 50 1

Hi
Advantages of using the double minus:
- slightly faster (approx. 5%)
- no problems if you have text values in your range (e.g. a heading row).
The multiplication syntax will fail the double minus not (will ignore text
cells)

But take a look at:
http://www.xldynamic.com/source/xld....CT.html#format

--
Regards
Frank Kabel
Frankfurt, Germany

Tim C wrote:
I multiply the terms "manually" instead of separating them with a
comma, thereby forcing the conversion without the double negative. I
end up with a single term, with SUMPRODUCT taking care of the adding
and the array handling. To me this results in a cleaner, more easily
understood formula.
Is this just a personal preference or are there advantages to doing
it Bob's way?

(My formula would be more concise if I took out the last set of
parenthesis, but I think the intent of the formula is more easily
understood with them in place. This can be very helpful for later
troubleshooting or modification.)
Tim C

"Peo Sjoblom" wrote in message
...
I wasn't consistent, Bob's formula was

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

"Tim C" wrote in message
...
Just curious. Why would you use

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))

instead of

=SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8))

Is there a speed issue? Or am I missing something else?

Thanks,
Tim C

"Peo Sjoblom" wrote in
message ...
One way

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))

Regards

Peo Sjoblom


"CMAC" wrote:

trying to do a formula along the lines of: search column b for
"n", if its an
"n" then multilply column d by column c. I need it to include all
occurances
though. so in the exapmle below it would include eeee, hhhh, and
kkkk. does anyone know what a proper formula would be?
many thanks.

A b c d
eeee n 60 1
ffff y 55 2
gggg j 12 1
hhhh n 50 2
iiii j 14 3
jjjj y 14 1
kkkk n 50 1

Frank,

Danke.

Tim C

"Frank Kabel" wrote in message
...
Hi
Advantages of using the double minus:
- slightly faster (approx. 5%)
- no problems if you have text values in your range (e.g. a heading row).
The multiplication syntax will fail the double minus not (will ignore text
cells)

But take a look at:
http://www.xldynamic.com/source/xld....CT.html#format

--
Regards
Frank Kabel
Frankfurt, Germany

Tim C wrote:
I multiply the terms "manually" instead of separating them with a
comma, thereby forcing the conversion without the double negative. I
end up with a single term, with SUMPRODUCT taking care of the adding
and the array handling. To me this results in a cleaner, more easily
understood formula.
Is this just a personal preference or are there advantages to doing
it Bob's way?

(My formula would be more concise if I took out the last set of
parenthesis, but I think the intent of the formula is more easily
understood with them in place. This can be very helpful for later
troubleshooting or modification.)
Tim C

"Peo Sjoblom" wrote in message
...
I wasn't consistent, Bob's formula was

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

"Tim C" wrote in message
...
Just curious. Why would you use

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))

instead of

=SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8))

Is there a speed issue? Or am I missing something else?

Thanks,
Tim C

"Peo Sjoblom" wrote in
message ...
One way

=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8))

Regards

Peo Sjoblom


"CMAC" wrote:

trying to do a formula along the lines of: search column b for
"n", if its an
"n" then multilply column d by column c. I need it to include all
occurances
though. so in the exapmle below it would include eeee, hhhh, and
kkkk. does anyone know what a proper formula would be?
many thanks.

A b c d
eeee n 60 1
ffff y 55 2
gggg j 12 1
hhhh n 50 2
iiii j 14 3
jjjj y 14 1
kkkk n 50 1