And,if,true Help

[Captain Grey]

This is what I want to achieve:

If the value in A1:A10 = ABC and the value in B1:B10 = DEF, then add the corresponding values in C1:C10 and multiply that by D1

I have tried:
=SUM(IF((A1:A10,"ABC")*(B1:B10,"DEF"),C1:C10,0))
but that doesn't work, and I haven't even got to multiplying the total of the sum_range.

Ultimately I need to complete that argument three times, where the values in the A, B and D columns would differ.

i.e.

(IF (A1:A10 = ABC and B1:B10 = DEF) ADD (C1:C10)*D1) PLUS
(IF (A1:A10 = GHI and B1:B10 = JKL) ADD (C1:C10)*D2) PLUS
(IF (A1:A10 = MNO and B1:B10 = PQR) ADD (C1:C10)*D3)

Can this be achieved in Excel? Is an IF statement the correct syntax?

Any help appreciated.

[Pete_UK]

Try this, then:

=SUM(IF((A1:A10="ABC")*(B1:B10="DEF"),C1:C10,0))*D1

This is an array formula, which means that once you have typed it in
(or subsequently edit it) then instead of using just <ENTER> you must
use CTRL-SHIFT-ENTER together. If you do this correctly then Excel will
wrap curly braces { } around the formula - you must not type these
yourself.

I'm not sure if you want one composite formula in your second part of
the posting, or three similar formulae.

Hope this helps.

Pete

[Captain Grey]

Thank you - that worked! My challenge now is to make one composite formula to cater for all three of my criteria as noted in the second part of my post. Are you able to save me hours on that one too?

[Captain Grey]

Actually, I think I've just cracked it. It doesn't look very elegant, but it seems do to the trick. Gawd knows how I'll pick up if it's referencing the wrong cells when I come to repeat the formula across 7 other cells. Thanks for your help!

[Pete_UK]

You can define named ranges for A1:A10, B1:B10 and C1:C10, using names
which are meaningful to you. You can also use names for the factors D1,
D2 etc. Then the formula will make more sense when you come to edit it
in the future. So, you might have something like:

=SUM(IF((names="ABC")*(parts="DEF"),cost,0))*factor1

Hope this helps.

Pete