#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Captain Grey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Captain Grey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Captain Grey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"