SumIf of SumProducts or summat...

  • Thread starter Thread starter RzB
  • Start date Start date
R

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
 
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)
 
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.
 
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
 
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.
 
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.
 
Peo, I *believe* Daniel lives in Canada and not in France ... same as
everyone thinking you live in Sweden <g>.

Regards,

Vasant.
 
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
 
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?
Click to expand...

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.
Click to expand...

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

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

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?
Click to expand...


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.
 
Daniel.M said:
Hi,


Menu Tools/Options View/Window Deselect Zero values.
Click to expand...

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
 
Daniel.M said:
Converts booleans (TRUE/FALSE) to numeric (1 or 0).
Click to expand...

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

Bob
 
Then I guess you know who Peter Forsberg is?

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

Daniel M.
 
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
 
Back
Top