Sumif for 2 variables

bbflouis

New Member
Joined
Feb 8, 2004
Messages
8
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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Feb 22, 2002
Messages
393
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
Joined
Feb 8, 2004
Messages
8
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
Joined
Jan 17, 2004
Messages
1,447

ADVERTISEMENT

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

bbflouis

New Member
Joined
Feb 8, 2004
Messages
8
apologize for formating mess, as a new user don't know how to insert excel examples to simplify helping
 

bbflouis

New Member
Joined
Feb 8, 2004
Messages
8

ADVERTISEMENT

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
Joined
Jan 17, 2004
Messages
1,447
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
Joined
Feb 8, 2004
Messages
8
That is exactly what I was looking for, thank you so much!!!!!! This will save me so much time.
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,447
Please note that the example I gave only gives a Average not a weighted average.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top