Help with this formula

  • Thread starter Thread starter Nelson
  • Start date Start date
N

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
 
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:
 
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.
 
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
 
Perhaps you intended
=sumproduct((B8:B23="D03KHLL")*(year(A8:A23)=2008),L8:L23) ?
 
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, 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)))
 
=sumproduct--((B8:B23="D03KHLL")*(year(A8:A23)=2008),(L8:L23))

Try it like this:

=SUMPRODUCT(--(YEAR(A8:A23)=2008),--(B8:B23="D03KHLL"),L8:L23)
 
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.
 
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?
 
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
Click to expand...

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
 
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.
 
Back
Top