Sumif for 2 variables

[bbflouis]

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

 

[just_jon]

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]

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]

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]

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

 

[bbflouis]

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

 

[bbflouis]

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]

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
	A	B	C	D	E
1	Pipe	Zone	Feb-04	Mar-04	Avgprice
2	MGT	1	10,000	10,000	0.25
3	TCPL	1	5,000	5,000	0.15
4	TCPL	2	80,939	86,521	0.2
5	MGT	2	10,000	10,000	0.35
6	ANR	1	17,342	15,221	0.3
7	ANR	2	69,629	70,711	0.4
8
9	Pipe	Zone	Feb-04	Mar-04
10	ANR	1	0.3	0.3
11	ANR	2	0.4	0.4
12	MGT	1	0.25	0.25
13	MGT	2	0.35	0.35
14	TCPL	1	0.15	0.15
15	TCPL	2	0.2	0.2
Sheet1

 

[bbflouis]

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

 

[GorD]

Please note that the example I gave only gives a Average not a weighted average.