# Sumif for 2 variables

#### bbflouis

##### New Member
Looking for help on using sumif for 2 variables, unable to find articles on the site?

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### just_jon

##### Legend
A bit skimpy on the details, but a concatenated COUNTIF or SUMPRODUCT come to mind. What is the layout you're working with? Rows/columns/type of data/sum what when?

EDIT -- Change that COUNT- to SUM- i'll wake up soon!

#### Albert 1

##### Active Member
bbflouis said:
Looking for help on using sumif for 2 variables, unable to find articles on the site?
Search this site for (e.g.): "sumif multiple criteria" and you will find many answers.

Albert 1

#### bbflouis

##### New Member
Thanks for your responses, apologize for lack of detail. Here is my problem below. Trying to determine my replacement cost for supplies already purchased. Problem in cross-referencing three inputs, pipe, zone and month? This is small example as I will have many more pipes, zones and months.

Have tried sumifs, dsum, arrays, nested formulas, etc. Can not get any to work, but I know it can work somehow.

Products purchased and associated units
Pipe Zone Feb-04 Mar-04
MGT 1 10,000 10,000
TCPL 1 5,000 5,000
TCPL 2 80,939 86,521
MGT 2 10,000 10,000
ANR 1 17,342 15,221
ANR 2 69,629 70,711

Replacement cost for products
Pipe Zone Feb-04 Mar-04
ANR 1 0.30 0.30
ANR 2 0.40 0.40
MGT 1 0.25 0.25
MGT 2 0.35 0.35
TCPL 1 0.15 0.15
TCPL 2 0.20 0.20

In above example will want an output that states for line 1 that replacement cost is 0.25 (MGT zone 1 for feb-mar), for line 2 replacement cost is 0.15 (TCPL zone 1 for feb-mar), etc.

#### GorD

##### Well-known Member

Are the repacement costs for each month always the same? What should happen if they are different?

#### bbflouis

##### New Member
apologize for formating mess, as a new user don't know how to insert excel examples to simplify helping

#### bbflouis

##### New Member

This example is simplified, if the replacement values are different (which they will be) will just need to multiply volume times replacement cost for each month and then divide by total volume to arrive at weighted average replacement cost. Biggest challenge I need assistance with is cross-referencing two seperate tables with 3 sets of criteria.

#### GorD

##### Well-known Member
I can do the first bit, so if you can do the second wer'e laughing.

Uses array formula, so enter with control shift enter
Book1
ABCDE
1PipeZoneFeb-04Mar-04Avgprice
2MGT110,00010,0000.25
3TCPL15,0005,0000.15
4TCPL280,93986,5210.2
5MGT210,00010,0000.35
6ANR117,34215,2210.3
7ANR269,62970,7110.4
8
9PipeZoneFeb-04Mar-04
10ANR10.30.3
11ANR20.40.4
12MGT10.250.25
13MGT20.350.35
14TCPL10.150.15
15TCPL20.20.2
Sheet1

#### bbflouis

##### New Member
That is exactly what I was looking for, thank you so much!!!!!! This will save me so much time.

#### GorD

##### Well-known Member
Please note that the example I gave only gives a Average not a weighted average.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,037
Messages
5,856,966
Members
431,841
Latest member
jaybeem

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back