Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria out,
the result is that I get all costs associated to that part number, but once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions



--
Nelson

Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria out,
the result is that I get all costs associated to that part number, but once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions



--
Nelson

Thanks but it didnt work, the one thing that I am confused about is the *
between the arrays.

I am not looking to multiply

Basically if part number X matchs in Rows 8- 23 with D03KHLL and of those
rows match only the year 2009 then add the associated figues together from
Column L8:L23

So if 2 out of the 4 rows match the criteria of part number D03khll and year
2009 add the cells in L8:L23 that match those rows

hope that clears it up.

--
Nelson


"Marcelo" wrote:

Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria out,
the result is that I get all costs associated to that part number, but once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions



--
Nelson

Nelson

I have undertood that yuo are looking or to sum the numbers you have in
column L (range L8:l23) whem both conditions are true i.é. b8:;b23 has
D03khll and the year of the dates on column A is equal to 2008. so the
sumproduct will work.

The * will multiply 1 (true) whem the first condition is true by 1 (true)
when the second conditon is true. if one of both are false it will 0 (zero).

Try this (I notice that was a "," before 2008 and the previous)
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Thanks but it didnt work, the one thing that I am confused about is the *
between the arrays.

I am not looking to multiply

Basically if part number X matchs in Rows 8- 23 with D03KHLL and of those
rows match only the year 2009 then add the associated figues together from
Column L8:L23

So if 2 out of the 4 rows match the criteria of part number D03khll and year
2009 add the cells in L8:L23 that match those rows

hope that clears it up.

--
Nelson


"Marcelo" wrote:

Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria out,
the result is that I get all costs associated to that part number, but once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions



--
Nelson

Thanks again however I am still getting the error that the formula I typed
contains an error.

I have looked through everything and cant seem to correct it

Any suggestions

--
Nelson


"Marcelo" wrote:

Nelson

I have undertood that yuo are looking or to sum the numbers you have in
column L (range L8:l23) whem both conditions are true i.é. b8:;b23 has
D03khll and the year of the dates on column A is equal to 2008. so the
sumproduct will work.

The * will multiply 1 (true) whem the first condition is true by 1 (true)
when the second conditon is true. if one of both are false it will 0 (zero).

Try this (I notice that was a "," before 2008 and the previous)
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Thanks but it didnt work, the one thing that I am confused about is the *
between the arrays.

I am not looking to multiply

Basically if part number X matchs in Rows 8- 23 with D03KHLL and of those
rows match only the year 2009 then add the associated figues together from
Column L8:L23

So if 2 out of the 4 rows match the criteria of part number D03khll and year
2009 add the cells in L8:L23 that match those rows

hope that clears it up.

--
Nelson


"Marcelo" wrote:

Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria out,
the result is that I get all costs associated to that part number, but once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions



--
Nelson

Perhaps you intended
=sumproduct((B8:B23="D03KHLL")*(year(A8:A23)=2008) ,L8:L23) ?
--
David Biddulph

"Marcelo" wrote in message
...
Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria
out,
the result is that I get all costs associated to that part number, but
once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions



--
Nelson

could you please send the formula that you are using?


--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Thanks again however I am still getting the error that the formula I typed
contains an error.

I have looked through everything and cant seem to correct it

Any suggestions

--
Nelson


"Marcelo" wrote:

Nelson

I have undertood that yuo are looking or to sum the numbers you have in
column L (range L8:l23) whem both conditions are true i.é. b8:;b23 has
D03khll and the year of the dates on column A is equal to 2008. so the
sumproduct will work.

The * will multiply 1 (true) whem the first condition is true by 1 (true)
when the second conditon is true. if one of both are false it will 0 (zero).

Try this (I notice that was a "," before 2008 and the previous)
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Thanks but it didnt work, the one thing that I am confused about is the *
between the arrays.

I am not looking to multiply

Basically if part number X matchs in Rows 8- 23 with D03KHLL and of those
rows match only the year 2009 then add the associated figues together from
Column L8:L23

So if 2 out of the 4 rows match the criteria of part number D03khll and year
2009 add the cells in L8:L23 that match those rows

hope that clears it up.

--
Nelson


"Marcelo" wrote:

Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria out,
the result is that I get all costs associated to that part number, but once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions



--
Nelson

Thanks again, I figured out what the error is I am hoping you can help me
with this.

Here is the working formula

=SUMPRODUCT(((YEAR(A8:A12)=2009))*(B8:B12="D03KHLL "),L8:L12)

If you noticed I shortened the rows to "12" and thats when the formula
worked the problem is rows 12 - 23 are blank thus leaving a #VALUE in those
blank cells that the formula is trying to work against. because this is a
template for 100 + other sheets some times rows 13 - 23 will be populated and
other times it wont be.

How can I get the formula to ignore the #VALUE in the blank cells

I should mention that these blank cells are being populated by this formula

=IF(ISERROR(INDEX(ALL,SMALL(IF(customername=B3,ROW (customername)),ROW(RawData!5:5)),7)),"",(INDEX(AL L,SMALL(IF(customername=B3,ROW(customername)),ROW( RawData!5:5)),7)))






--
Nelson


"David Biddulph" wrote:

Perhaps you intended
=sumproduct((B8:B23="D03KHLL")*(year(A8:A23)=2008) ,L8:L23) ?
--
David Biddulph

"Marcelo" wrote in message
...
Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria
out,
the result is that I get all costs associated to that part number, but
once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions



--
Nelson

=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Marcelo" wrote in message
...
could you please send the formula that you are using?


--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Thanks again however I am still getting the error that the formula I
typed
contains an error.

I have looked through everything and cant seem to correct it

Any suggestions

--
Nelson


"Marcelo" wrote:

Nelson

I have undertood that yuo are looking or to sum the numbers you have in
column L (range L8:l23) whem both conditions are true i.é. b8:;b23 has
D03khll and the year of the dates on column A is equal to 2008. so the
sumproduct will work.

The * will multiply 1 (true) whem the first condition is true by 1
(true)
when the second conditon is true. if one of both are false it will 0
(zero).

Try this (I notice that was a "," before 2008 and the previous)
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Thanks but it didnt work, the one thing that I am confused about is
the *
between the arrays.

I am not looking to multiply

Basically if part number X matchs in Rows 8- 23 with D03KHLL and of
those
rows match only the year 2009 then add the associated figues together
from
Column L8:L23

So if 2 out of the 4 rows match the criteria of part number D03khll
and year
2009 add the cells in L8:L23 that match those rows

hope that clears it up.

--
Nelson


"Marcelo" wrote:

Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is
2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year
criteria out,
the result is that I get all costs associated to that part
number, but once I
throw in the need to break out the cost per differing year I get
nothing.

Any suggestions



--
Nelson

Try this array formula** :

=SUM(IF(ISNUMBER(A8:A12),IF((YEAR(A8:A12)=2009)*(B 8:B12="D03KHLL"),L8:L12)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Thanks again, I figured out what the error is I am hoping you can help me
with this.

Here is the working formula

=SUMPRODUCT(((YEAR(A8:A12)=2009))*(B8:B12="D03KHLL "),L8:L12)

If you noticed I shortened the rows to "12" and thats when the formula
worked the problem is rows 12 - 23 are blank thus leaving a #VALUE in
those
blank cells that the formula is trying to work against. because this is a
template for 100 + other sheets some times rows 13 - 23 will be populated
and
other times it wont be.

How can I get the formula to ignore the #VALUE in the blank cells

I should mention that these blank cells are being populated by this
formula

=IF(ISERROR(INDEX(ALL,SMALL(IF(customername=B3,ROW (customername)),ROW(RawData!5:5)),7)),"",(INDEX(AL L,SMALL(IF(customername=B3,ROW(customername)),ROW( RawData!5:5)),7)))






--
Nelson


"David Biddulph" wrote:

Perhaps you intended
=sumproduct((B8:B23="D03KHLL")*(year(A8:A23)=2008) ,L8:L23) ?
--
David Biddulph

"Marcelo" wrote in message
...
Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is
2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria
out,
the result is that I get all costs associated to that part number, but
once I
throw in the need to break out the cost per differing year I get
nothing.

Any suggestions



--
Nelson

Thanks That worked GREAT! BUT now a new problem, I tried to add 4 other part
numers to the function and it does not seem to be working (I am now getting 0)

here is what I am trying to do

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23)))

this does not seem to be working they way I have read this to work both in
sum and sumproduct

Any suggestions?
--
Nelson


"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISNUMBER(A8:A12),IF((YEAR(A8:A12)=2009)*(B 8:B12="D03KHLL"),L8:L12)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Thanks again, I figured out what the error is I am hoping you can help me
with this.

Here is the working formula

=SUMPRODUCT(((YEAR(A8:A12)=2009))*(B8:B12="D03KHLL "),L8:L12)

If you noticed I shortened the rows to "12" and thats when the formula
worked the problem is rows 12 - 23 are blank thus leaving a #VALUE in
those
blank cells that the formula is trying to work against. because this is a
template for 100 + other sheets some times rows 13 - 23 will be populated
and
other times it wont be.

How can I get the formula to ignore the #VALUE in the blank cells

I should mention that these blank cells are being populated by this
formula

=IF(ISERROR(INDEX(ALL,SMALL(IF(customername=B3,ROW (customername)),ROW(RawData!5:5)),7)),"",(INDEX(AL L,SMALL(IF(customername=B3,ROW(customername)),ROW( RawData!5:5)),7)))






--
Nelson


"David Biddulph" wrote:

Perhaps you intended
=sumproduct((B8:B23="D03KHLL")*(year(A8:A23)=2008) ,L8:L23) ?
--
David Biddulph

"Marcelo" wrote in message
...
Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is
2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria
out,
the result is that I get all costs associated to that part number, but
once I
throw in the need to break out the cost per differing year I get
nothing.

Any suggestions



--
Nelson

On Tue, 9 Jun 2009 06:35:01 -0700, Nelson
wrote:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is 2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year criteria out,
the result is that I get all costs associated to that part number, but once I
throw in the need to break out the cost per differing year I get nothing.

Any suggestions

Try this for 2008:

=SUMIFS(L8:L23,B8:B23,"D03KHLL",A8:A23,"="&DATE(2 008,1,1),A8:A23,"<="&DATE(2008,12,31))

Or, with 2008 in, for example A1:

=SUMIFS(L8:L23,B8:B23,"D03KHLL",A8:A23,"="&DATE(A 1,1,1),A8:A23,"<="&DATE(A1,12,31))

Or, if looking at two random dates to sum between, with the earlier date in A1
and the later date in A2:


=SUMIFS(L8:L23,B8:B23,"D03KHLL",A8:A23,"="&A1,A8: A23,"<="&A2)


--ron

List your part numbers in a range of cells, say, A1:A3...

A1 = D03KHLL
A2 = D03KJLL
A3 = D03KILL

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(I SNUMBER(MATCH(B8:B23,A1:A3,0))),L8:L23)))

Still array entered.

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Thanks That worked GREAT! BUT now a new problem, I tried to add 4 other
part
numers to the function and it does not seem to be working (I am now
getting 0)

here is what I am trying to do

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23)))

this does not seem to be working they way I have read this to work both in
sum and sumproduct

Any suggestions?
--
Nelson


"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISNUMBER(A8:A12),IF((YEAR(A8:A12)=2009)*(B 8:B12="D03KHLL"),L8:L12)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Thanks again, I figured out what the error is I am hoping you can help
me
with this.

Here is the working formula

=SUMPRODUCT(((YEAR(A8:A12)=2009))*(B8:B12="D03KHLL "),L8:L12)

If you noticed I shortened the rows to "12" and thats when the formula
worked the problem is rows 12 - 23 are blank thus leaving a #VALUE in
those
blank cells that the formula is trying to work against. because this
is a
template for 100 + other sheets some times rows 13 - 23 will be
populated
and
other times it wont be.

How can I get the formula to ignore the #VALUE in the blank cells

I should mention that these blank cells are being populated by this
formula

=IF(ISERROR(INDEX(ALL,SMALL(IF(customername=B3,ROW (customername)),ROW(RawData!5:5)),7)),"",(INDEX(AL L,SMALL(IF(customername=B3,ROW(customername)),ROW( RawData!5:5)),7)))






--
Nelson


"David Biddulph" wrote:

Perhaps you intended
=sumproduct((B8:B23="D03KHLL")*(year(A8:A23)=2008) ,L8:L23) ?
--
David Biddulph

"Marcelo" wrote in message
...
Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is
2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year
criteria
out,
the result is that I get all costs associated to that part number,
but
once I
throw in the need to break out the cost per differing year I get
nothing.

Any suggestions



--
Nelson

Excellent, and thanks. I missed this one earlier.

Much appreciated
--
Nelson


"T. Valko" wrote:

List your part numbers in a range of cells, say, A1:A3...

A1 = D03KHLL
A2 = D03KJLL
A3 = D03KILL

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(I SNUMBER(MATCH(B8:B23,A1:A3,0))),L8:L23)))

Still array entered.

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Thanks That worked GREAT! BUT now a new problem, I tried to add 4 other
part
numers to the function and it does not seem to be working (I am now
getting 0)

here is what I am trying to do

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23)))

this does not seem to be working they way I have read this to work both in
sum and sumproduct

Any suggestions?
--
Nelson


"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISNUMBER(A8:A12),IF((YEAR(A8:A12)=2009)*(B 8:B12="D03KHLL"),L8:L12)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Thanks again, I figured out what the error is I am hoping you can help
me
with this.

Here is the working formula

=SUMPRODUCT(((YEAR(A8:A12)=2009))*(B8:B12="D03KHLL "),L8:L12)

If you noticed I shortened the rows to "12" and thats when the formula
worked the problem is rows 12 - 23 are blank thus leaving a #VALUE in
those
blank cells that the formula is trying to work against. because this
is a
template for 100 + other sheets some times rows 13 - 23 will be
populated
and
other times it wont be.

How can I get the formula to ignore the #VALUE in the blank cells

I should mention that these blank cells are being populated by this
formula

=IF(ISERROR(INDEX(ALL,SMALL(IF(customername=B3,ROW (customername)),ROW(RawData!5:5)),7)),"",(INDEX(AL L,SMALL(IF(customername=B3,ROW(customername)),ROW( RawData!5:5)),7)))






--
Nelson


"David Biddulph" wrote:

Perhaps you intended
=sumproduct((B8:B23="D03KHLL")*(year(A8:A23)=2008) ,L8:L23) ?
--
David Biddulph

"Marcelo" wrote in message
...
Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1 is
2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year
criteria
out,
the result is that I get all costs associated to that part number,
but
once I
throw in the need to break out the cost per differing year I get
nothing.

Any suggestions



--
Nelson

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Excellent, and thanks. I missed this one earlier.

Much appreciated
--
Nelson


"T. Valko" wrote:

List your part numbers in a range of cells, say, A1:A3...

A1 = D03KHLL
A2 = D03KJLL
A3 = D03KILL

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(I SNUMBER(MATCH(B8:B23,A1:A3,0))),L8:L23)))

Still array entered.

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Thanks That worked GREAT! BUT now a new problem, I tried to add 4 other
part
numers to the function and it does not seem to be working (I am now
getting 0)

here is what I am trying to do

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23)))

this does not seem to be working they way I have read this to work both
in
sum and sumproduct

Any suggestions?
--
Nelson


"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISNUMBER(A8:A12),IF((YEAR(A8:A12)=2009)*(B 8:B12="D03KHLL"),L8:L12)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nelson" wrote in message
...
Thanks again, I figured out what the error is I am hoping you can
help
me
with this.

Here is the working formula

=SUMPRODUCT(((YEAR(A8:A12)=2009))*(B8:B12="D03KHLL "),L8:L12)

If you noticed I shortened the rows to "12" and thats when the
formula
worked the problem is rows 12 - 23 are blank thus leaving a #VALUE
in
those
blank cells that the formula is trying to work against. because
this
is a
template for 100 + other sheets some times rows 13 - 23 will be
populated
and
other times it wont be.

How can I get the formula to ignore the #VALUE in the blank cells

I should mention that these blank cells are being populated by this
formula

=IF(ISERROR(INDEX(ALL,SMALL(IF(customername=B3,ROW (customername)),ROW(RawData!5:5)),7)),"",(INDEX(AL L,SMALL(IF(customername=B3,ROW(customername)),ROW( RawData!5:5)),7)))






--
Nelson


"David Biddulph" wrote:

Perhaps you intended
=sumproduct((B8:B23="D03KHLL")*(year(A8:A23)=2008) ,L8:L23) ?
--
David Biddulph

"Marcelo" wrote in message
...
Hello Nelson

try
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23),=2008),(L8:L23))

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

Here is what I have

=SUMIFS((L8:L23, B8:B23),"=D03KHLL",year(A8:A23),=2008))


L8:L23 are the amounts

Criteria 1
B8:B23 (which are the part numbers )
="=D05555" a part

Criteria 2
and year(A8:A23) the corresponding part number from criteria 1
is
2009

give me the result from adding those matching costs from L8:L23

here is the kicker, I can get this to work if I take the year
criteria
out,
the result is that I get all costs associated to that part
number,
but
once I
throw in the need to break out the cost per differing year I get
nothing.

Any suggestions



--
Nelson