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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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,446

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,446
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,446
Please note that the example I gave only gives a Average not a weighted average.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,658
Members
414,325
Latest member
kfg1287

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