Help with this formula

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