Trouble with formula

[Bruce Benaway]

For some reason, I am having trouble getting this to work. Created the
following formula:

=SUMPRODUCT(('Incoming Cores'!A1:A4="Duncan")+('Incoming
Cores'!A1:A4="Simplet"))*'Incoming Cores'!B1:E4)

The actual spreadsheet covers a much larger area. Simply wanting the sum of
the Duncan and Simplet rows. Can anyone see what I have down wrong?


a b c
d e

1 smith 33 44 55
66
2 Duncan 45 56 45
38
3 Simplet 74 65 26
67
4 Duncan 6 78 67
69

 

[Luke M]

=SUMPRODUCT(((A1:A4="Duncan")+(A1:A4="Simplet"))*B1:C4)

You were missing a parenthesis near the beginning.

 

[Bruce Benaway]

Thanks much, it worked. Have a general question as this has happened to me
before. How do you know when one, two or three parenthesis are needed before
and after some of these formulas. Thanks again.

 

[David Biddulph]

It's not a question of how many parentheses "before" or "after" a formula.
The first thing to remember is that parentheses come in pairs, so you need
the same number of opening parentheses as closing. You had 3 opening and 4
closing, so Excel will have told you that the formula was not valid.

You just need to think about what you are trying to do, and in what order.
The SUMPRODUCT function encloses its parameters in parentheses, so that is
your outside pair. You wanted to multiply B1:E4 by another term, so the
other term goes in another pair of parentheses to force it to be calculated
before the multiplication. That term was to be calculated by adding your
two terms dependent on A1:A4, so each of these is calculated first (by being
put in another pair of parentheses) before being added.