Using IF formulas on multiple columns

[gideonr]

I have a table with Multiple columns and rows like this:

Transaction Month1 Month2 Month3 etc.. Location

Sales 500 550 340 Glasgow
Sales 780 825 575 Edinburgh
Sales 260 345 210 Inverness
Expenses 500 550 340 Glasgow
Expenses 780 825 575 Edinburgh
Expenses 260 345 210 Inverness

In another excel sheet I need to have an IF formula which says
IF(B3:B50="Sales",IF(F3:F50="Glasgow,C3:C50,0)) so that when the value
in column B = Sales and the value in Column F = Glasgow it returns the
equivalent value from Column C. At the moment this formula id giving
me a #VALUE! error message.

Gideon

 

[Anthony Slater]

Look up SUMPRODUCT formula

=SUMPRODUCT((A1:A6="sales")*(E1:E6="Glasgow")*C1:C6)

adjust as neccessary

 

[Jason Morin]

=INDEX(C3:C50,MATCH(1,(B3:B50="Sales")*
(F3:F50="Glasgow"),0))

Array-entered, meaning press ctrl/shift/enter, not just
enter, after inserting the formula.

HTH
Jason
Atlanta, GA