IF FUCTION WITH 2 SETS OF CRITERIA

[Mark D]

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.

 

[Per Jessen]

Hi
Try this sumproduct formula:

=Sumproduct(--(A1:A100="London"),--(B1:B100=4),C1:C100)

Regards,
Per

 

[Teethless mama]

=sumproduct((a1:a3="London")*(b1:b3=4),c1:c4)

 

[Teethless mama]

Oops...

Column C should be c1:c3

 

[Jacob Skaria]

Try the below
=SUMPRODUCT(($A$1:$A$100="London")*($B$1:$B$100=4),$C$1:$C$100)

If this post helps click Yes

 

[Mark D]

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]

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

 

[sally t]

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