SumIf of SumProducts or summat...

[RzB]

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

 

[Bob Phillips]

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)

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Peo Sjoblom]

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

 

[Peo Sjoblom]

Hi Norman,

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

 

[Norman Harker]

Thanks Peo. I missed that point.

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Peo Sjoblom]

Slick as always Daniel, working nightshift? <g>

 

[Daniel.M]

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.

 

[Daniel.M]

... working nightshift? <g>

Nope. Evening shift (approx. 21:30)

Daniel M.

 

[Vasant Nanavati]

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

Regards,

Vasant.

 

[RzB]

All - many thanks for your help. I'm a bit of a newbie
re Excel formula. I agree with Peo's comment about "..."
instead of zeroes. Problem is that I have a table with
totals at the bottom and it's important to be able to see
the "shape" of the data. With zeroes visible that
does not show up. I could set zeroes off at the
worksheet level but I want to be able to have the totals
row showing the zeroes. I don't think it is possible to
selectively suppress display of zeros - is it?

Daniels solution works just fine. However I do not
understand what the -- is doing, or why it does not
work without them.

I found an earlier post saying.....
The -- is two unary minuses, i.e., --X is -(-X). The --
idiom has the advantage of binding to its operand more
tightly than any other operator can, so it minimizes the
chance of formula bugs.

I don't understand what "binding to its operand more
tightly" means....

In my reading since posting I have found that True/False
evaluates to 1 or 0 if used in an arithmetic formula.
So why the need for the double negative?

Many thanks,
Roy

 

[Daniel.M]

Hi,

the "shape" of the data. With zeroes visible that
does not show up. I could set zeroes off at the
worksheet level but I want to be able to have the totals
row showing the zeroes. I don't think it is possible to
selectively suppress display of zeros - is it?

Menu Tools/Options View/Window Deselect Zero values.

Daniels solution works just fine. However I do not
understand what the -- is doing, or why it does not
work without them.

Converts booleans (TRUE/FALSE) to numeric (1 or 0).

I don't understand what "binding to its operand more
tightly" means....

It's all related to operator precedance. Some operators are executed before
others (* is executed before +). Although what is said is true, that's not the
main reason I use it. I just like the idea since I've seen it the first time
(Harlan Grove published it first if I recall) and if it's not faster than *1 or
+0 on spreadsheet's calcs (in any means we can verify), it's certainly IS faster
on a lot of compilers

In my reading since posting I have found that True/False
evaluates to 1 or 0 if used in an arithmetic formula.
So why the need for the double negative?

It's the key sentence here: it's NOT used in an arithmetic formula (not multiply
or addition). That last argument stands on its own. That's why you must transfer
it from booleans to 1 or 0.

Regards,

Daniel M.

 

[Peo Sjoblom]

Hi,


Menu Tools/Options View/Window Deselect Zero values.

Or if you don't want the whole workbook like that custom format
two examples

General;-General;


$#,##0.00_);($#,##0.00);


So, are you Canadian Daniel?


Regards,

Peo Sjoblom

 

[Bob Phillips]

Converts booleans (TRUE/FALSE) to numeric (1 or 0).

Bit picky, but a single - converts booleans to numeric (-1 or 0). The
extra - is just to negate the -1.

Bob

 

[RzB]

OK - Now I understand...
Many thanks to everyone for your excellent help.
Roy

 

[Daniel.M]

So, are you Canadian Daniel?

Yep.

 

[Peo Sjoblom]

Then I guess you know who Peter Forsberg is?

 

[Daniel.M]

Then I guess you know who Peter Forsberg is?

You bet. And I'd take him on my team anytime.

Daniel M.

 

[J.E. McGimpsey]

Then I guess you know who Peter Forsberg is?

The enemy.

 

[RzB]

Ooops... There seems to be case where things
don't work quite right.

If it turns out that if there is only one row in my table
then I get the dreaded #VALUE in columns that have the "...".
Hmmm after Peo's first post I took out all the "..."s, then put
them back in after Daniels suggestion seemed to work so well!

These formula are being generated automatically from an
Access (VBA) application. So perhaps I should detect that I
have only one line and do it a different way?

Any thoughts?

Roy