sum for match criteria

I am trying to get a sum of part cost for each year

I have a table with dates that are between 39814 (2007) and 40908 (2011)

this is Column A, in column D are the prices for the parts

So I want to find the sum of all part costs purchased between 39814 and
39813 (2007) then I will do the same for 2008,2009, 2010 etc.

Any suggestions



--
Neall

Hi,
In a new column let's assume Column E starting in E1 enter 2007 down to 2011
then in F1 enter

=sumproduct(E1=$A$1:$A$1000),$D$1:$D$1000)
Copy the formula down

Change the range to fit your needs, but remember range has to be identical
in both sides of the formula (A and D)

If this helps please click yes, thanks

"Neall" wrote:

I am trying to get a sum of part cost for each year

I have a table with dates that are between 39814 (2007) and 40908 (2011)

this is Column A, in column D are the prices for the parts

So I want to find the sum of all part costs purchased between 39814 and
39813 (2007) then I will do the same for 2008,2009, 2010 etc.

Any suggestions



--
Neall

=sumif(A:A,"=" & date(2007,1,1),D) - sumif(A:A,"=" & date(2008,1,1),D)
will add up the costs for parts purchased in 2007. The first sumif counts
everything purchased on or after 1/1/07; the second subtracts those purhcased
on or after 1/1/08, so the difference is what was purchased in 2007.

"Neall" wrote:

I am trying to get a sum of part cost for each year

I have a table with dates that are between 39814 (2007) and 40908 (2011)

this is Column A, in column D are the prices for the parts

So I want to find the sum of all part costs purchased between 39814 and
39813 (2007) then I will do the same for 2008,2009, 2010 etc.

Any suggestions



--
Neall

With dates in column A...

List the year numbers in a range of cells:

F1 = 2007
F2 = 2008
F3 = 2009
etc
etc

Enter this formula G1 and copy down to G3:

=SUMPRODUCT(--(YEAR(A$1:A$10)=F1),D$1$10)

--
Biff
Microsoft Excel MVP


"Neall" wrote in message
...
I am trying to get a sum of part cost for each year

I have a table with dates that are between 39814 (2007) and 40908 (2011)

this is Column A, in column D are the prices for the parts

So I want to find the sum of all part costs purchased between 39814 and
39813 (2007) then I will do the same for 2008,2009, 2010 etc.

Any suggestions



--
Neall

I have a follow up question now

I need to add 2 other variables to your answer of

=SUMIF(A8:A23,"=" & DATE(2007,1,1),L8:L23) - SUMIF(A8:A23,"=" &
DATE(2008,1,1),L8:L23)

Now I need to say count if part numbers = abc

I need to break this up now into 2 different part categories and these 2
part categories have about 6 different part numbers each.

Thanks

--
Neall


"bapeltzer" wrote:

=sumif(A:A,"=" & date(2007,1,1),D) - sumif(A:A,"=" & date(2008,1,1),D)
will add up the costs for parts purchased in 2007. The first sumif counts
everything purchased on or after 1/1/07; the second subtracts those purhcased
on or after 1/1/08, so the difference is what was purchased in 2007.

"Neall" wrote:

I am trying to get a sum of part cost for each year

I have a table with dates that are between 39814 (2007) and 40908 (2011)

this is Column A, in column D are the prices for the parts

So I want to find the sum of all part costs purchased between 39814 and
39813 (2007) then I will do the same for 2008,2009, 2010 etc.

Any suggestions



--
Neall

Now that you've got criteria on multiple fields is when I switch from sumif
or countif to sumproduct. If you want to get the total cost of 'abc' parts
in 2007 (I'll assume the part number is in column B):
=SUMIF(--(year(A8:A23)=2007),--(B8:B23="abc"),D823)


"Neall" wrote:

I have a follow up question now

I need to add 2 other variables to your answer of

=SUMIF(A8:A23,"=" & DATE(2007,1,1),L8:L23) - SUMIF(A8:A23,"=" &
DATE(2008,1,1),L8:L23)

Now I need to say count if part numbers = abc

I need to break this up now into 2 different part categories and these 2
part categories have about 6 different part numbers each.

Thanks

--
Neall


"bapeltzer" wrote:

=sumif(A:A,"=" & date(2007,1,1),D) - sumif(A:A,"=" & date(2008,1,1),D)
will add up the costs for parts purchased in 2007. The first sumif counts
everything purchased on or after 1/1/07; the second subtracts those purhcased
on or after 1/1/08, so the difference is what was purchased in 2007.

"Neall" wrote:

I am trying to get a sum of part cost for each year

I have a table with dates that are between 39814 (2007) and 40908 (2011)

this is Column A, in column D are the prices for the parts

So I want to find the sum of all part costs purchased between 39814 and
39813 (2007) then I will do the same for 2008,2009, 2010 etc.

Any suggestions



--
Neall

Thanks, so this is what I have

=SUMPRODUCT(--(YEAR(A8:A23)=2009),--(B8:B23="D03KHLL"),L8:L23)

But I seem to be missing the end criteria where it gives me the sum of the
criteria met of L8:L23

Any suggestions
--
Neall


"bapeltzer" wrote:

Now that you've got criteria on multiple fields is when I switch from sumif
or countif to sumproduct. If you want to get the total cost of 'abc' parts
in 2007 (I'll assume the part number is in column B):
=SUMIF(--(year(A8:A23)=2007),--(B8:B23="abc"),D823)


"Neall" wrote:

I have a follow up question now

I need to add 2 other variables to your answer of

=SUMIF(A8:A23,"=" & DATE(2007,1,1),L8:L23) - SUMIF(A8:A23,"=" &
DATE(2008,1,1),L8:L23)

Now I need to say count if part numbers = abc

I need to break this up now into 2 different part categories and these 2
part categories have about 6 different part numbers each.

Thanks

--
Neall


"bapeltzer" wrote:

=sumif(A:A,"=" & date(2007,1,1),D) - sumif(A:A,"=" & date(2008,1,1),D)
will add up the costs for parts purchased in 2007. The first sumif counts
everything purchased on or after 1/1/07; the second subtracts those purhcased
on or after 1/1/08, so the difference is what was purchased in 2007.

"Neall" wrote:

I am trying to get a sum of part cost for each year

I have a table with dates that are between 39814 (2007) and 40908 (2011)

this is Column A, in column D are the prices for the parts

So I want to find the sum of all part costs purchased between 39814 and
39813 (2007) then I will do the same for 2008,2009, 2010 etc.

Any suggestions



--
Neall