This is a formula that I have now

=sumproduct((B2:B2002)="Machine")*(Year(A2:A2002)= 2006)
*(Month(A2:A2002)=1)*J2:J2002)

this works great (thanks to every one on this forum)

any ways I would like to add another condition in there.I tried the
following but
holy what a number did I get

=sumproduct((B2:B2002="Machine")*(C2:C2002="Repair s")*(Year(A2:A2002)=2006)*(Month(A2:A2002)=1)*J2:J 2002)

any help in the right direction will be greatly apprec.

Why do you think your formula didn't work?

hellZg8 wrote:

This is a formula that I have now

=sumproduct((B2:B2002)="Machine")*(Year(A2:A2002)= 2006)
*(Month(A2:A2002)=1)*J2:J2002)

this works great (thanks to every one on this forum)

any ways I would like to add another condition in there.I tried the
following but
holy what a number did I get

=sumproduct((B2:B2002="Machine")*(C2:C2002="Repair s")*(Year(A2:A2002)=2006)*(Month(A2:A2002)=1)*J2:J 2002)

any help in the right direction will be greatly apprec.

--

Dave Peterson

I'm not entirerly sure. I guess I still don't quite understanding how the
formula is actually working.

It looks for the Value "Machine" in the Array1 (B2:B2002) and then finds the
Dates
in Array2 (A2:A2002) then sums the adjacent cells corresponding with Array's
1 & 2.

It almost looks as though it is summing everything up.

"Dave Peterson" wrote:

Why do you think your formula didn't work?

hellZg8 wrote:

This is a formula that I have now

=sumproduct((B2:B2002)="Machine")*(Year(A2:A2002)= 2006)
*(Month(A2:A2002)=1)*J2:J2002)

this works great (thanks to every one on this forum)

any ways I would like to add another condition in there.I tried the
following but
holy what a number did I get

=sumproduct((B2:B2002="Machine")*(C2:C2002="Repair s")*(Year(A2:A2002)=2006)*(Month(A2:A2002)=1)*J2:J 2002)

any help in the right direction will be greatly apprec.

--

Dave Peterson

For testing purposes...

You could apply Data|Filter|autofilter to that range (all those columns).

Show just the cells with Machine in column b, just the cells with Repairs in
column C and just the January 2006 dates in column A.

Then put =subtotal(9,j:j) in a helper cell.

======
Or use a helper cell with a formula like:

=if(and(b2="machine",c2="repairs",text(a2,"yyyymm" )="200601"),j2,0)

and drag down the column.

Then sum that column.

It looks like your formula is ok to me--maybe there's just more stuff than you
think.


hellZg8 wrote:

I'm not entirerly sure. I guess I still don't quite understanding how the
formula is actually working.

It looks for the Value "Machine" in the Array1 (B2:B2002) and then finds the
Dates
in Array2 (A2:A2002) then sums the adjacent cells corresponding with Array's
1 & 2.

It almost looks as though it is summing everything up.

"Dave Peterson" wrote:

Why do you think your formula didn't work?

hellZg8 wrote:

This is a formula that I have now

=sumproduct((B2:B2002)="Machine")*(Year(A2:A2002)= 2006)
*(Month(A2:A2002)=1)*J2:J2002)

this works great (thanks to every one on this forum)

any ways I would like to add another condition in there.I tried the
following but
holy what a number did I get

=sumproduct((B2:B2002="Machine")*(C2:C2002="Repair s")*(Year(A2:A2002)=2006)*(Month(A2:A2002)=1)*J2:J 2002)

any help in the right direction will be greatly apprec.

--

Dave Peterson


--

Dave Peterson

thanks for your response Dave

This is what I would like to do

sum all amounts in column J2:J2002 that match up with a certain machine and
for repairs only within a certain month.

Machine A Repairs Totals for Janurary = $$$$$

Do this for each Month, Quarter, and Year To Date

I have not yet tried your suggestion, thought maybe give a bit more clarity
to what I would like to achieve.
Tks in advance

"Dave Peterson" wrote:

For testing purposes...

You could apply Data|Filter|autofilter to that range (all those columns).

Show just the cells with Machine in column b, just the cells with Repairs in
column C and just the January 2006 dates in column A.

Then put =subtotal(9,j:j) in a helper cell.

======
Or use a helper cell with a formula like:

=if(and(b2="machine",c2="repairs",text(a2,"yyyymm" )="200601"),j2,0)

and drag down the column.

Then sum that column.

It looks like your formula is ok to me--maybe there's just more stuff than you
think.


hellZg8 wrote:

I'm not entirerly sure. I guess I still don't quite understanding how the
formula is actually working.

It looks for the Value "Machine" in the Array1 (B2:B2002) and then finds the
Dates
in Array2 (A2:A2002) then sums the adjacent cells corresponding with Array's
1 & 2.

It almost looks as though it is summing everything up.

"Dave Peterson" wrote:

Why do you think your formula didn't work?

hellZg8 wrote:

This is a formula that I have now

=sumproduct((B2:B2002)="Machine")*(Year(A2:A2002)= 2006)
*(Month(A2:A2002)=1)*J2:J2002)

this works great (thanks to every one on this forum)

any ways I would like to add another condition in there.I tried the
following but
holy what a number did I get

=sumproduct((B2:B2002="Machine")*(C2:C2002="Repair s")*(Year(A2:A2002)=2006)*(Month(A2:A2002)=1)*J2:J 2002)

any help in the right direction will be greatly apprec.

--

Dave Peterson


--

Dave Peterson

You may want to look at Data|Pivottables.

There's lots of ways you can summarize your data, but using pivottables can make
it simpler.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

hellZg8 wrote:

thanks for your response Dave

This is what I would like to do

sum all amounts in column J2:J2002 that match up with a certain machine and
for repairs only within a certain month.

Machine A Repairs Totals for Janurary = $$$$$

Do this for each Month, Quarter, and Year To Date

I have not yet tried your suggestion, thought maybe give a bit more clarity
to what I would like to achieve.
Tks in advance

"Dave Peterson" wrote:

For testing purposes...

You could apply Data|Filter|autofilter to that range (all those columns).

Show just the cells with Machine in column b, just the cells with Repairs in
column C and just the January 2006 dates in column A.

Then put =subtotal(9,j:j) in a helper cell.

======
Or use a helper cell with a formula like:

=if(and(b2="machine",c2="repairs",text(a2,"yyyymm" )="200601"),j2,0)

and drag down the column.

Then sum that column.

It looks like your formula is ok to me--maybe there's just more stuff than you
think.


hellZg8 wrote:

I'm not entirerly sure. I guess I still don't quite understanding how the
formula is actually working.

It looks for the Value "Machine" in the Array1 (B2:B2002) and then finds the
Dates
in Array2 (A2:A2002) then sums the adjacent cells corresponding with Array's
1 & 2.

It almost looks as though it is summing everything up.

"Dave Peterson" wrote:

Why do you think your formula didn't work?

hellZg8 wrote:

This is a formula that I have now

=sumproduct((B2:B2002)="Machine")*(Year(A2:A2002)= 2006)
*(Month(A2:A2002)=1)*J2:J2002)

this works great (thanks to every one on this forum)

any ways I would like to add another condition in there.I tried the
following but
holy what a number did I get

=sumproduct((B2:B2002="Machine")*(C2:C2002="Repair s")*(Year(A2:A2002)=2006)*(Month(A2:A2002)=1)*J2:J 2002)

any help in the right direction will be greatly apprec.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson