If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IF FUCTION WITH 2 SETS OF CRITERIA
Good afternoon all, I am looking for some help if possible
I need excel to take a sum based on 2 sets of critiera I put cells in just for reference For example Office Band Amount A1: London B1: 4 C1: 2000 A2: Geneva B2: 3 C2: 3000 A3: Stuttgart B3: 2 C3: 4000 So I am trying to do the following =IF(A1="London" AND, B1=4,c1) I know the calculation above isnt right but I am trying like mad to work it out and am stuck. Any help greatly appreciated. |
#2
|
|||
|
|||
IF FUCTION WITH 2 SETS OF CRITERIA
Hi
Try this sumproduct formula: =Sumproduct(--(A1:A100="London"),--(B1:B100=4),C1:C100) Regards, Per "Mark D" skrev i meddelelsen ... Good afternoon all, I am looking for some help if possible I need excel to take a sum based on 2 sets of critiera I put cells in just for reference For example Office Band Amount A1: London B1: 4 C1: 2000 A2: Geneva B2: 3 C2: 3000 A3: Stuttgart B3: 2 C3: 4000 So I am trying to do the following =IF(A1="London" AND, B1=4,c1) I know the calculation above isnt right but I am trying like mad to work it out and am stuck. Any help greatly appreciated. |
#3
|
|||
|
|||
IF FUCTION WITH 2 SETS OF CRITERIA
=sumproduct((a1:a3="London")*(b1:b3=4),c1:c4)
"Mark D" wrote: Good afternoon all, I am looking for some help if possible I need excel to take a sum based on 2 sets of critiera I put cells in just for reference For example Office Band Amount A1: London B1: 4 C1: 2000 A2: Geneva B2: 3 C2: 3000 A3: Stuttgart B3: 2 C3: 4000 So I am trying to do the following =IF(A1="London" AND, B1=4,c1) I know the calculation above isnt right but I am trying like mad to work it out and am stuck. Any help greatly appreciated. |
#4
|
|||
|
|||
IF FUCTION WITH 2 SETS OF CRITERIA
Oops...
Column C should be c1:c3 "Mark D" wrote: Good afternoon all, I am looking for some help if possible I need excel to take a sum based on 2 sets of critiera I put cells in just for reference For example Office Band Amount A1: London B1: 4 C1: 2000 A2: Geneva B2: 3 C2: 3000 A3: Stuttgart B3: 2 C3: 4000 So I am trying to do the following =IF(A1="London" AND, B1=4,c1) I know the calculation above isnt right but I am trying like mad to work it out and am stuck. Any help greatly appreciated. |
#5
|
|||
|
|||
IF FUCTION WITH 2 SETS OF CRITERIA
Try the below
=SUMPRODUCT(($A$1:$A$100="London")*($B$1:$B$100=4) ,$C$1:$C$100) If this post helps click Yes --------------- Jacob Skaria "Mark D" wrote: Good afternoon all, I am looking for some help if possible I need excel to take a sum based on 2 sets of critiera I put cells in just for reference For example Office Band Amount A1: London B1: 4 C1: 2000 A2: Geneva B2: 3 C2: 3000 A3: Stuttgart B3: 2 C3: 4000 So I am trying to do the following =IF(A1="London" AND, B1=4,c1) I know the calculation above isnt right but I am trying like mad to work it out and am stuck. Any help greatly appreciated. |
#6
|
|||
|
|||
IF FUCTION WITH 2 SETS OF CRITERIA
Thanks for all the replies, but one more quick question
What if it's Geneva and Level 2?? Do i just replicate the sumproduct formula?? "Jacob Skaria" wrote: Try the below =SUMPRODUCT(($A$1:$A$100="London")*($B$1:$B$100=4) ,$C$1:$C$100) If this post helps click Yes --------------- Jacob Skaria "Mark D" wrote: Good afternoon all, I am looking for some help if possible I need excel to take a sum based on 2 sets of critiera I put cells in just for reference For example Office Band Amount A1: London B1: 4 C1: 2000 A2: Geneva B2: 3 C2: 3000 A3: Stuttgart B3: 2 C3: 4000 So I am trying to do the following =IF(A1="London" AND, B1=4,c1) I know the calculation above isnt right but I am trying like mad to work it out and am stuck. Any help greatly appreciated. |
#7
|
|||
|
|||
IF FUCTION WITH 2 SETS OF CRITERIA
You can refer that to a cell
D1 = Geneva E1 = 5 =SUMPRODUCT(($A$1:$A$100=D1)*($B$1:$B$100=E1),$C$1 :$C$100) If this post helps click Yes --------------- Jacob Skaria "Mark D" wrote: Thanks for all the replies, but one more quick question What if it's Geneva and Level 2?? Do i just replicate the sumproduct formula?? "Jacob Skaria" wrote: Try the below =SUMPRODUCT(($A$1:$A$100="London")*($B$1:$B$100=4) ,$C$1:$C$100) If this post helps click Yes --------------- Jacob Skaria "Mark D" wrote: Good afternoon all, I am looking for some help if possible I need excel to take a sum based on 2 sets of critiera I put cells in just for reference For example Office Band Amount A1: London B1: 4 C1: 2000 A2: Geneva B2: 3 C2: 3000 A3: Stuttgart B3: 2 C3: 4000 So I am trying to do the following =IF(A1="London" AND, B1=4,c1) I know the calculation above isnt right but I am trying like mad to work it out and am stuck. Any help greatly appreciated. |
#8
|
|||
|
|||
IF FUCTION WITH 2 SETS OF CRITERIA
Hi. This is probably far too late because it looks like you've solved it
another way, but I just thought you were on the right lines with your IF and AND except your AND was in the wrong place and you didn't finish off the IF statement results at the end. The following would have worked fine, which is near to what you had: =IF(AND(A1="london",B1=1),C1,"") The 2 speech marks at the end of the IF would leave a blank cell if the criteria of London and 1 were not met. You can continue adding ANDs to look at up to 30 different columns so this is just the AND nested once to look at 2 columns. Sall "Jacob Skaria" wrote: You can refer that to a cell D1 = Geneva E1 = 5 =SUMPRODUCT(($A$1:$A$100=D1)*($B$1:$B$100=E1),$C$1 :$C$100) If this post helps click Yes --------------- Jacob Skaria "Mark D" wrote: Thanks for all the replies, but one more quick question What if it's Geneva and Level 2?? Do i just replicate the sumproduct formula?? "Jacob Skaria" wrote: Try the below =SUMPRODUCT(($A$1:$A$100="London")*($B$1:$B$100=4) ,$C$1:$C$100) If this post helps click Yes --------------- Jacob Skaria "Mark D" wrote: Good afternoon all, I am looking for some help if possible I need excel to take a sum based on 2 sets of critiera I put cells in just for reference For example Office Band Amount A1: London B1: 4 C1: 2000 A2: Geneva B2: 3 C2: 3000 A3: Stuttgart B3: 2 C3: 4000 So I am trying to do the following =IF(A1="London" AND, B1=4,c1) I know the calculation above isnt right but I am trying like mad to work it out and am stuck. Any help greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|