Formula help

  • Thread starter Thread starter gms1
  • Start date Start date
G

gms1

Hello first post here. I have a list of company names in column A. In column
B I have what's called material group numbers. They are usually 1-40
sometimes less (1-20). And in column C I have psi values. These range
anywhere from 100,000 to 400,000. And the psi value is associated with the
material group number from column B. Here is what it might look like.

Column A = Seco, Mitsubishi, Iscar
Column B = 1,2,3,4,5,6,7,8,9,10
Column C = 120000, 280000, 240000, 150000

What I want to do is input the company name in one cell, input the material
group number in another cell and based on those 2 cells have the formula
input the correct psi value in another cell. The company name cell and
material group cell will never change so those are locked in place. The list
of material groups and psi values will change as I add more over time.

I have a good idea how I want this to go I just have no idea how to format
the formula in Excel to work. Like IF D5=Anycorp AND D6=5 then pick a number
from this list. Any help would be appreciated and thanks in advance!
 
Setup your table like a flat file database:

Seco...1...120000
Seco...2...280000

Seco...3...240000
Seco...4...150000

Then, to find Seco group 3:

=SUMPRODUCT(--(A1:A4="seco"),--(B1:B4=3),C1:C4)

Or, using cells to hold the criteria:

E1 = Seco
F1 = 3

=SUMPRODUCT(--(A1:A4=E1),--(B1:B4=F1),C1:C4)
 
That first formula worked perfect for what I wanted. I use P3 for the company
name, P4 for material group number and I stuck the flat database at the
bottom of the sheet which will be deleted and formatted after. So here is my
final formula:

=SUMPRODUCT(--(A101:A120=P3),--(B101:B120=P4), C101:C120)

and that works perfect thanks!
 
Out of curiosity, what if I wanted to access the flat database in another
excel workbook instead of the same sheet? Instead of accessing A101:A120 for
company names, how would I, or could I access a different excel file?
 
You need to include the path to the source file.

Have the source file open.

Start typing the formula:

=SUMPRODUCT(--(

Now, use your mouse and point to the ranges in the source file. Assume the
source file is named Data.xls. Excel will add the path for you:

=SUMPRODUCT(--([Data.xls]Sheet1!$A$101:$A$120

Then finish adding the criteria:

=SUMPRODUCT(--([Data.xls]Sheet1!$A$101:$A$120=P3),--(

Your formula will end up looking like this:

=SUMPRODUCT(--([Data.xls]Sheet1!$A$101:$A$120=P3),--([Data.xls]Sheet1!$B$101:$B$120=P4),[Data.xls]Sheet1!$C$101:$C$120)
 
Back
Top