adding multiple sums w/ different criteria

[Doug]

I have been able to partialy add the sums for the following table using sum(if

1/9/07
g h i j
k l
desc. ser. code date rec. cost/day status cost to date
date ret.
xxx xxx 100 1/1/07 $100 here $200
xxx xxx 200 1/1/07 $100 here $200

xxx xxx 100 1/1/07 $100 return $200
1/5/07
xxx xxx 300 1/1/07 $100 here $200

xxx xxx 100 1/1/07 $100 enroute $200


What I am attempting to do is add all cells for a daily cost where the
equipt. with the same cost code is either still here or enroute, and make it
quit adding up items on a daily ticket for parts returned. the formula I am
using is

=SUM(IF((G6:G12="100")+(J6:J12="here"),I6:I12))

The problem is that it adds the items here even though it is the incorrect
cost code. Any help is greatly appreciated as I take this info and add to a
second worksheet w/ the indivdual cost / per day of each code broke down.

 

[Murray]

Doug

An array formula should do this.

=sum(if((G6:G12=100)*(J6:J12="here"),I6:I12,false))

Type in the formula and press CTRL+SHIFT+ENTER to enter it.

Watch out for the relative/absoulte references if you intend to move
this around elsewhere.

More info on arrays at Chip Pearsons site
http://www.cpearson.com/excel/array.htm

Good luck

Murray

 

[Doug]

This only gets an answer of true for me. It still is not adding the cells and
giving me a total. What I need it to do is add all cells which are code 100
and are here or enroute. I do no want it to count cells which are coded 100
and are returned.
Thanks for the help just not quite there.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(G6:G12=100),(J6:J12="here")+(J6:J12="enroute"),I6:I12)

 

[Doug]

With that I get the #NAME error.

One way:

=SUMPRODUCT(--(G6:G12=100),(J6:J12="here")+(J6:J12="enroute"),I6:I12)

 

[JE McGimpsey]

Are you using a non-English version of XL? The SUMPRODUCT() function may
have a different local name. If you're using an English version, you
made an error in typing or copying the formula.

 

[Murray]

Doug

Your original formula only tried to count the "here"'s, not the
"enroute"s. That can be overcome, but nevertheless using your data as
posted and summing data where code=100 and status=here should give an
answer of 100 from column I. Is that correct?

The formula I posted works when I do it and should not give a value of
true. Are you sure you entered it correctly.

If you want to mail me the sheet (or part of it) I can have a look.

Regards

Murray