adding multiple sums w/ different criteria
 

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.

adding multiple sums w/ different criteria
 

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 wrote:
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.

adding multiple sums w/ different criteria
 

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.

"Murray" wrote:

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 wrote:
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.

adding multiple sums w/ different criteria
 

One way:

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


In article ,
Doug wrote:
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.

"Murray" wrote:

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 wrote:
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.

adding multiple sums w/ different criteria
 

With that I get the #NAME error.

"JE McGimpsey" wrote:

One way:

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


In article ,
Doug wrote:
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.

"Murray" wrote:

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 wrote:
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.

adding multiple sums w/ different criteria
 

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.

In article ,
Doug wrote:

With that I get the #NAME error.

"JE McGimpsey" wrote:

One way:

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

adding multiple sums w/ different criteria
 

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



Doug wrote:
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.

"Murray" wrote:

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 wrote:
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.