sumproduct if
 

i need a "sumproductif" function:, a combination of sumif and sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in column
C holds

does anyone know of anything that would do this??

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria.
Now if I understand rightly, you have one criteria and several columns to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY", what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria.
Now if I understand rightly, you have one criteria and several columns to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

=SUMPRODUCT((A1:A4="BUY")*(B1:B4)*(C1:C4))

HTH. Best wishes Harald

"sumproduct if" <sumproduct skrev i melding
...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY", what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several columns
to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans
le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and
sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

=SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10))

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans le
message de news: ...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY", what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several columns to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

=SUMPRODUCT(--(C1:C10="a"),A1:A10)+SUMPRODUCT(--(C1:C10="a"),B1:B10)

but SUMIF works as well in your example

=SUMIF(C1:C10,"a",A1:A10)+SUMIF(C1:C10,"a",B1:B10)

--
HTH

Bob Phillips

"sumproduct if" <sumproduct wrote in message
...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY", what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several columns
to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans
le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and
sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

Hello Harald
In the example your formula will amount to 50 which means it adds values
twice?

Cordially
Pascal

"Harald Staff" a écrit dans le message de news:
...
=SUMPRODUCT((A1:A4="BUY")*(B1:B4)*(C1:C4))

HTH. Best wishes Harald

"sumproduct if" <sumproduct skrev i melding
...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY", what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several columns
to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans
le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and
sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

.... or:
=SUMPRODUCT((A1:A11="BUY")*(B1:B11+C1:C11))

--
pozdrav!
Berislav

papou wrote:
=SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10))

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit
dans le message de news:
...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY",
what would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several
columns to add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit
dans le message de news:
...
i need a "sumproductif" function:, a combination of sumif and
sumproduct

e.g. i need to sumproduct values in columns A and B if a condition
in column
C holds

does anyone know of anything that would do this??

"papou" skrev i melding
...
Hello Harald
In the example your formula will amount to 50 which means it adds values
twice?

I thought the OP wanted something like
=SUMPRODUCT(B1:B4,C1:C4)
with the criteria added, and that
=SUMIF(A1:A4,"BUY",B1:B4)+SUMIF(A1:A4,"BUY",C1:C4)
was the not the thing. Guess I was wrong.

Best wishes Harald

"IF" the idea is to get 25 as the sum for all Buy's then this should do
it.

=SUMPRODUCT((J2:J5="Buy")*K2:L5)

--
Don Guillett
SalesAid Software

"sumproduct if" <sumproduct wrote in message
...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY", what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several columns
to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans
le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and
sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

A little more concise:

=SUMPRODUCT((A1:A10="BUY")*((B1:B10)+(C1:C10)))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"papou" wrote in message
...
=SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10))

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans le
message de news: ...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY", what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several columns to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

or even more so

=SUMPRODUCT((A1:A10="BUY")*(B1:C10))


--
HTH

Bob Phillips

"RagDyeR" wrote in message
...
A little more concise:

=SUMPRODUCT((A1:A10="BUY")*((B1:B10)+(C1:C10)))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"papou" wrote in message
...
=SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10))

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans le
message de news: ...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY",
what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several columns
to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans
le
message de news: ...
i need a "sumproductif" function:, a combination of sumif and
sumproduct

e.g. i need to sumproduct values in columns A and B if a condition in
column
C holds

does anyone know of anything that would do this??

I knew that when Don posted his at 5:30 this morning, and I didn't see it in
my O.E. for some reason.
I kicked myself when I finally did see his.
Are you repeating it here so that I kick myself again?<vbg

I wish these MS servers would get it all together, so that I could at least
put my foot in my mouth less often!

--
Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Bob Phillips" wrote in message
...
or even more so

=SUMPRODUCT((A1:A10="BUY")*(B1:C10))


--
HTH

Bob Phillips

"RagDyeR" wrote in message
...
A little more concise:

=SUMPRODUCT((A1:A10="BUY")*((B1:B10)+(C1:C10)))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"papou" wrote in message
...
=SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10))

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit dans
le
message de news: ...
can you give me an example? Say i have:

A B C
BUY 2 4
BUY 4 8
SELL 3 4
BUY 5 2

if i want to sumproduct columns B and C only where column A = "BUY",
what
would be the formula?




"papou" wrote:

Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one
criteria.
Now if I understand rightly, you have one criteria and several
columns
to
add.
In which case you may add several SUMPRODUCT together.

HTH
Cordially
Pascal

"sumproduct if" <sumproduct a écrit
dans
le
message de news:
...
i need a "sumproductif" function:, a combination of sumif and
sumproduct

e.g. i need to sumproduct values in columns A and B if a condition
in
column
C holds

does anyone know of anything that would do this??

"RagDyer" wrote in message
...
I knew that when Don posted his at 5:30 this morning, and I didn't see it
in
my O.E. for some reason.
I kicked myself when I finally did see his.
Are you repeating it here so that I kick myself again?<vbg

Not at all. I hadn't seen Don's reply when I sent this, I just sent it as
a small ironic riposte as mine was even more long-winded (I'm English don't
forget, we do lots of irony ;-))

I wish these MS servers would get it all together, so that I could at
least
put my foot in my mouth less often!

Amen, then I would have seen Don's too. I still miss posts on this thing!