Whew!

Sometimes when you have extra ()'s in a formula, it just gets more confusing
when you try to figure out what you did later on.

hellZg8 wrote:

My mistake I must have had a typo in the very first one I did and didn't even
notice it.
Dave you are right it returns the same results.

Thank you for you time with this.

"Dave Peterson" wrote:

Except for a typo in the second formula (on too many close parenthesis at the
far right), both of those expressions returned the same value for me.

I still don't see any difference in the results between your two formulas.

=sum((((a1:a20))))
will return the same as
=sum(a1:a20)


hellZg8 wrote:

Hi Dave
Here is the orginal formula that I started with

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

now other than changing the Year and Month to the beginning all I did was
add the
parentahese to the beginning and end of the formula.

=sumproduct((((Year(A2:A2002)=2006)*(Month(A2:A200 2)=1)*(B2:B2002="Machine")*(C2:C2002="Repairs")*J2 :J2002))))

"Dave Peterson" wrote:

I thought so <bg. But I still don't see a difference in results between your
this formula and your original.

hellZg8 wrote:

Hi Dave Happy New Year

That was a typo on my behalf still one finger typer

=sumproduct((((Year(A2:A2002)=2006)*(Month(A2:A200 2)=1)*(B2:B2002="Machine")*(C2:C2002="Repairs")*J2 :J2002))))

"Dave Peterson" wrote:

I think you're missing the ending quotes around Machine, but your original
formula still looks ok to me.

hellZg8 wrote:

Hi Dave
I would like to take this opportunity to thank you for your time helping
me with this.
Your first response to my question was
Why do you think your formula didn't work?

Well I kept looking at the formula and looking ,and looking and finally i
tried this

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

now if I'm understanding this a little bit more I think the formula only
seen range B2:B2002, C2:C2002 as 1 array not 2 because of my lack of open and
closed brackets.
once again thanks for your help and input

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson