SumIf of SumProducts or summat...

I have a column of numbers and text Col A. The text is just a blanking "---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy

Roy,

Try something like

=SUM(ISNUMBER(A1:B100)*(T1:T100=criteria),(A1:B100 ))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"RzB" wrote in message
...
I have a column of numbers and text Col A. The text is just a blanking
"---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy

Hi Roy!

Try:

=SUMPRODUCT((A1:A7)*(B1:B7)*(C1:C7="y"))

Items in A are multiplied by corresponding item in B and then
multiplied by 1 if C item is y or 0 if C item is not y. SUMPRODUCT
adds the results.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"RzB" wrote in message
...
I have a column of numbers and text Col A. The text is just a
blanking "---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy

I don't think that will work, you probably meant

=SUMPRODUCT((ISNUMBER(A1:B100)*(T1:T100="criteria" )),A1:B100)

or the array entered

=SUM(IF((ISNUMBER(A1:B100))*(T1:T100="criteria"),( A1:B100)))


I would also caution the OP of not using text in the A:B range, it is
better
to use 0 for zero values as opposed to text "--", further down the road
with other formulas they might cause problems

--

Regards,

Peo Sjoblom

"Bob Phillips" wrote in message
...
Roy,

Try something like

=SUM(ISNUMBER(A1:B100)*(T1:T100=criteria),(A1:B100 ))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"RzB" wrote in message
...
I have a column of numbers and text Col A. The text is just a blanking
"---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy

Hi Norman,

won't work since the OP has text in A:B

--

Regards,

Peo Sjoblom

"Norman Harker" wrote in message
...
Hi Roy!

Try:

=SUMPRODUCT((A1:A7)*(B1:B7)*(C1:C7="y"))

Items in A are multiplied by corresponding item in B and then
multiplied by 1 if C item is y or 0 if C item is not y. SUMPRODUCT
adds the results.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"RzB" wrote in message
...
I have a column of numbers and text Col A. The text is just a
blanking "---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy

Thanks Peo. I missed that point.

I certainly agree with advice not to blank out with text.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Peo Sjoblom" wrote in message
...
Hi Norman,

won't work since the OP has text in A:B

--

Regards,

Peo Sjoblom

"Norman Harker" wrote in message
...
Hi Roy!

Try:

=SUMPRODUCT((A1:A7)*(B1:B7)*(C1:C7="y"))

Items in A are multiplied by corresponding item in B and then
multiplied by 1 if C item is y or 0 if C item is not y. SUMPRODUCT
adds the results.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and
Arguments)
available free to good homes.
"RzB" wrote in message
...
I have a column of numbers and text Col A. The text is just a
blanking "---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but
only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and
books...
Can anyone offer any help...
Many thanks,
Roy

Slick as always Daniel, working nightshift? g

--

Regards,

Peo Sjoblom

"Daniel.M" wrote in message
...
Hi,

=SUMPRODUCT(A1:A100,B1:B100,--(C1:C100="criteria"))

SUMPRODUCT has the ability to handle text appropriately if you're not
multiply
it explicitly within the formula (same way SUM() does it).

Regards,

Daniel M.

"RzB" wrote in message
...
I have a column of numbers and text Col A. The text is just a blanking
"---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy

Hi,

=SUMPRODUCT(A1:A100,B1:B100,--(C1:C100="criteria"))

SUMPRODUCT has the ability to handle text appropriately if you're not multiply
it explicitly within the formula (same way SUM() does it).

Regards,

Daniel M.

"RzB" wrote in message
...
I have a column of numbers and text Col A. The text is just a blanking "---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy

... working nightshift? g

Nope. Evening shift :-) (approx. 21:30)

Daniel M.

Peo, I *believe* Daniel lives in Canada and not in France ... same as
everyone thinking you live in Sweden g.

Regards,

Vasant.


"Peo Sjoblom" wrote in message
...
Slick as always Daniel, working nightshift? g

--

Regards,

Peo Sjoblom

"Daniel.M" wrote in message
...
Hi,

=SUMPRODUCT(A1:A100,B1:B100,--(C1:C100="criteria"))

SUMPRODUCT has the ability to handle text appropriately if you're not
multiply
it explicitly within the formula (same way SUM() does it).

Regards,

Daniel M.

"RzB" wrote in message
...
I have a column of numbers and text Col A. The text is just a blanking
"---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy