Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And,if,true Help
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:C1)*D2) PLUS (IF (A1:A10 = MNO and B1:B10 = PQR) ADD (C1:C1)*D3) Can this be achieved in Excel? Is an IF statement the correct syntax? Any help appreciated. -- Captain Grey ------------------------------------------------------------------------ Captain Grey's Profile: http://www.excelforum.com/member.php...o&userid=33804 View this thread: http://www.excelforum.com/showthread...hreadid=535751 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And,if,true Help
Try this, then:
=SUM(IF((A1:A10="ABC")*(B1:B10="DEF"),C1:C10,0))*D 1 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And,if,true Help
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 ------------------------------------------------------------------------ Captain Grey's Profile: http://www.excelforum.com/member.php...o&userid=33804 View this thread: http://www.excelforum.com/showthread...hreadid=535751 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And,if,true Help
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! -- Captain Grey ------------------------------------------------------------------------ Captain Grey's Profile: http://www.excelforum.com/member.php...o&userid=33804 View this thread: http://www.excelforum.com/showthread...hreadid=535751 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And,if,true Help
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))*facto r1 Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|