Help with this formula

[Nelson]

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

 

[Marcelo]

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:

 

[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]

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

 

[David Biddulph]

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

 

[Marcelo]

could you please send the formula that you are using?


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



"Nelson" escreveu:

 

[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(ALL,SMALL(IF(customername=B3,ROW(customername)),ROW(RawData!5:5)),7)))

 

[T. Valko]

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

Try it like this:

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

 

[T. Valko]

Try this array formula** :

=SUM(IF(ISNUMBER(A8:A12),IF((YEAR(A8:A12)=2009)*(B8: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.

 

[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)*(B8: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?

 

[Ron Rosenfeld]

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(2008,1,1),A8:A23,"<="&DATE(2008,12,31))

Or, with 2008 in, for example A1:

=SUMIFS(L8:L23,B8:B23,"D03KHLL",A8:A23,">="&DATE(A1,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

 

[T. Valko]

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)*(ISNUMBER(MATCH(B8:B23,A1:A3,0))),L8:L23)))

Still array entered.

 

[Nelson]

Excellent, and thanks. I missed this one earlier.

Much appreciated

 

[T. Valko]

You're welcome. Thanks for the feedback!