programming question

  • Thread starter Theodore Bartley
  • Start date
T

Theodore Bartley

Hi, I am doing a calculation on the numbers in column B (say), unless there
is an X in the adjacent cell in column C, in which case I must leave those
numbers out of the calculation. I can't set them to 0 because that would
cause an error - I have to ignore them.

It may be dificult to give a detailed explanation here, but I'd be grateful
for some principles. I'm not even sure if this is a job for VBA or if it
can be done on the worksheet?

Thanks,
Theo
 
R

routeram

Hi,

use an if statement to put a number from column B or zero in column
and sum column D up.

Ram
 
T

Theodore Bartley

This so nearly does what I want it to do! But I can't figure out how to set
the criteria to "not X and not P". I've tried all logical combinations I
can think of, but I can only set the criteria to simple expressions like
"<>X". Is it not possible to use more complex expressions?

Thanks,
Theo
 
H

Harald Staff

Hi Theo

No wonder why you struggle; SUMIF won't do multiple criteria. Instead try

=SUMPRODUCT((C1:C100<>"x")*(C1:C100<>"p")*(B1:B100))

HTH. Best wishes Harald
 
T

Theo Bartley

Perfect!! That is such a great help.
I've never come across sumproduct before.

Thanks, Harald.

Theo
 
T

Theo Bartley

Harald, it's always 2 steps forward and 1 back with me!

Your formula works perfectly with R1C1 notation, and that's fine, I can go
with that, but I recently spent several hours learning about names and
labels and I like them and want to use them wherever possible. However, it
seems I haven't understood names as well as I thought. I'm trying to use
=SUMPRODUCT((LXP<>"x")*(LXP<>"p")*(Loan)), where LXP and Loan are defined
names. They are actually column labels. They work fine in other formulae
like SUM, but this is giving an error - "A value used in the formula is of
the wrong data type". I've also tried
=SUMPRODUCT(--(LXP<>"x")*--(LXP<>"p")*(Loan)), but get the same error. Also
tried =SUMPRODUCT((--LXP<>"x")*(--LXP<>"p")*(Loan)), and
=SUMPRODUCT((-LXP<>"x")*(-LXP<>"p")*(Loan)).

I really would like to use my labels if at all possible. Thanks, Theo.
 
H

Harald Staff

Hi Theo

LXP and Loan must be of exact same sizes. This will also err:
=SUMPRODUCT((C1:C100<>"x")*(C45:C89<>"p")*(B12:B15))
so question 1 are they the same shapes and sizes and question 2, does this
err:
=SUMPRODUCT((LXP<>"x")*(LXP<>"p"))
?

SUMPRODUCT used like this is a less messy way to use an "array formula". See
http://www.cpearson.com/excel/array.htm
on those things, it's very useful.

Best wishes Harald
 
T

Theodore Bartley

Hi Harald,

Both the same shape and size: Loan is defined as =June!$C$7:$C$15 and LXP
as =June!$D$7:$D$15.

=SUMPRODUCT((LXP<>"x")*(LXP<>"p")) doesn't error, but it gives an answer I
don't understand (=6 for two x's and one p).

(I believe the definitions of the names are correct in that the first cell
contains the label, ie. $C$7 contains 'Loan', and $D$7 contains 'LXP'; that
is how it should be isn't it?)

Thanks,
Theo
 
H

Harald Staff

Theodore Bartley said:
Hi Harald,

Both the same shape and size: Loan is defined as =June!$C$7:$C$15 and LXP
as =June!$D$7:$D$15.

=SUMPRODUCT((LXP<>"x")*(LXP<>"p")) doesn't error, but it gives an answer I
don't understand (=6 for two x's and one p).
Click to expand...

It's the count of cells not containing x or p. Now try
=SUMPRODUCT((LXP<>"x")*(Loan<>"p"))
if that errs then it's the name definition or the size of the Loan range
that's wrong. Fix it with menu Insert > Name > Define.

HTH. Best wishes Harald
 
T

Theo Bartley

Hi Harald,

Thanks for all your help. All works sweetly now. The problem was that my
definitions of names included the name itself in the range. For example,
LXP was defined as =June!$D$7:$D$15, where the label LXP was stored in $D$7.
When I define all my ranges to exclude the labels themselves, the SUMPRODUCT
formula works fine.

When I read about names and labels, the book said to define a label by
selecting the range, INCLUDING THE LABEL at the top, then use
Insert/Name/Create, which I did and which works fine for some formulae such
as SUM and SUMIF.

Anyway, seems to me it's better not to include the label in the range.

Thanks again for your help. Everything else I need to do is quite
straightforward. I think.

Theo
 
Top