sumif
 

I need to keep track of maintenance on certain machines and how much it costs.
Would like to be able to show how much was spent on each machine in a given
month or qtr.

any Ideas

Tks

sumif
 

=SUMPRODUCT((A1:A100="machine")*(YEAR(B1:B100)=200 5)*(MONTH(B1:B100)={1,2,3}
)*C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
I need to keep track of maintenance on certain machines and how much it
costs.
Would like to be able to show how much was spent on each machine in a
given
month or qtr.

any Ideas

Tks

sumif
 

assuming
dates machine cost
=sumproduct((month(a2:a22)=3)*(b2:b22="machine1")* c2:c22)

--
Don Guillett
SalesAid Software

"hellZg8" wrote in message
...
I need to keep track of maintenance on certain machines and how much it
costs.
Would like to be able to show how much was spent on each machine in a
given
month or qtr.

any Ideas

Tks

sumif
 

Tks for the quick response.
The spreadsheet I'm using is set up like a database.I have 2 columns for
Date. The first only shows the month, the second shows the actual date the
main. was done.
The first column is the one I need (I think).

Here's my question
For the month of January for Machine #1 what was the total cost of
maintenance?

so for every Month in column1 that = January and for every machine #1 =
column 2
sum up all cost .

I'm not sure if it should be treated as an array (I've tried both ways same
result)

Tks

"Bob Phillips" wrote:

=SUMPRODUCT((A1:A100="machine")*(YEAR(B1:B100)=200 5)*(MONTH(B1:B100)={1,2,3}
)*C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
I need to keep track of maintenance on certain machines and how much it
costs.
Would like to be able to show how much was spent on each machine in a
given
month or qtr.

any Ideas

Tks

sumif
 

The problem with having just a separate month column is that there can be
data for say Jan 2005 and Jan 2006, so I added a test for the year.

This should still get you the cost for Jan 2005

=SUMPRODUCT(($A$1:$A$100="Machine
#1")*(YEAR($C$1:$C$100)=2005)*(MONTH($C$1:$C$100)= 1)*$D$1:$D$100)

and this will get the first quarter

=SUMPRODUCT(($A$1:$A$100="Machine
#1")*(YEAR($C$1:$C$100)=2005)*(MONTH($C$1:$C$100)= {1,2,3})*$D$1:$D$100)

where column A holds the machine data, C holds the date, and D the amount.
Just change to suit


--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
Tks for the quick response.
The spreadsheet I'm using is set up like a database.I have 2 columns for
Date. The first only shows the month, the second shows the actual date the
main. was done.
The first column is the one I need (I think).

Here's my question
For the month of January for Machine #1 what was the total cost of
maintenance?

so for every Month in column1 that = January and for every machine #1 =
column 2
sum up all cost .

I'm not sure if it should be treated as an array (I've tried both ways
same
result)

Tks

"Bob Phillips" wrote:


=SUMPRODUCT((A1:A100="machine")*(YEAR(B1:B100)=200 5)*(MONTH(B1:B100)={1,2,3}
)*C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
I need to keep track of maintenance on certain machines and how much
it
costs.
Would like to be able to show how much was spent on each machine in a
given
month or qtr.

any Ideas

Tks

sumif
 

this is the formula I have right know (in a seperate workbook)
=SUMPRODUCT((YEAR(A1:A10)=2005*(MONTH(A1:A10)=11)* (C1:C10="H-540")*E1:E10))
this gives me a $0.00 total when I should be at a $3500.00 total
The format for first column I had the month in was general , I tried typing
in the number for the month and the name same result.
I switch the format to Month-Day-Year

Ctrl+Shift+Enter ?? after didn't work either

"Bob Phillips" wrote:

The problem with having just a separate month column is that there can be
data for say Jan 2005 and Jan 2006, so I added a test for the year.

This should still get you the cost for Jan 2005

=SUMPRODUCT(($A$1:$A$100="Machine
#1")*(YEAR($C$1:$C$100)=2005)*(MONTH($C$1:$C$100)= 1)*$D$1:$D$100)

and this will get the first quarter

=SUMPRODUCT(($A$1:$A$100="Machine
#1")*(YEAR($C$1:$C$100)=2005)*(MONTH($C$1:$C$100)= {1,2,3})*$D$1:$D$100)

where column A holds the machine data, C holds the date, and D the amount.
Just change to suit


--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
Tks for the quick response.
The spreadsheet I'm using is set up like a database.I have 2 columns for
Date. The first only shows the month, the second shows the actual date the
main. was done.
The first column is the one I need (I think).

Here's my question
For the month of January for Machine #1 what was the total cost of
maintenance?

so for every Month in column1 that = January and for every machine #1 =
column 2
sum up all cost .

I'm not sure if it should be treated as an array (I've tried both ways
same
result)

Tks

"Bob Phillips" wrote:


=SUMPRODUCT((A1:A100="machine")*(YEAR(B1:B100)=200 5)*(MONTH(B1:B100)={1,2,3}
)*C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
I need to keep track of maintenance on certain machines and how much
it
costs.
Would like to be able to show how much was spent on each machine in a
given
month or qtr.

any Ideas

Tks

sumif
 

Watch those parentheses!

=SUMPRODUCT((YEAR(A1:A10)=2005)*(MONTH(A1:A10)=11) *(C1:C10="H-540")*E1:E10))
you had:
=SUMPRODUCT((YEAR(A1:A10)=2005*(MONTH(A1:A10)=11)* (C1:C10="H-540")*E1:E10))

One of these things is not like the other <vbg.

And one more way:

=SUMPRODUCT(--(TEXT(A1:A10,"yyyymm")="200511"),--(C1:C10="H-540"),(E1:E10))

=sumproduct() likes to work with numbers. The -- stuff converts true's to 1's
and false's to 0's.




hellZg8 wrote:

this is the formula I have right know (in a seperate workbook)
=SUMPRODUCT((YEAR(A1:A10)=2005*(MONTH(A1:A10)=11)* (C1:C10="H-540")*E1:E10))
this gives me a $0.00 total when I should be at a $3500.00 total
The format for first column I had the month in was general , I tried typing
in the number for the month and the name same result.
I switch the format to Month-Day-Year

Ctrl+Shift+Enter ?? after didn't work either

"Bob Phillips" wrote:

The problem with having just a separate month column is that there can be
data for say Jan 2005 and Jan 2006, so I added a test for the year.

This should still get you the cost for Jan 2005

=SUMPRODUCT(($A$1:$A$100="Machine
#1")*(YEAR($C$1:$C$100)=2005)*(MONTH($C$1:$C$100)= 1)*$D$1:$D$100)

and this will get the first quarter

=SUMPRODUCT(($A$1:$A$100="Machine
#1")*(YEAR($C$1:$C$100)=2005)*(MONTH($C$1:$C$100)= {1,2,3})*$D$1:$D$100)

where column A holds the machine data, C holds the date, and D the amount.
Just change to suit


--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
Tks for the quick response.
The spreadsheet I'm using is set up like a database.I have 2 columns for
Date. The first only shows the month, the second shows the actual date the
main. was done.
The first column is the one I need (I think).

Here's my question
For the month of January for Machine #1 what was the total cost of
maintenance?

so for every Month in column1 that = January and for every machine #1 =
column 2
sum up all cost .

I'm not sure if it should be treated as an array (I've tried both ways
same
result)

Tks

"Bob Phillips" wrote:


=SUMPRODUCT((A1:A100="machine")*(YEAR(B1:B100)=200 5)*(MONTH(B1:B100)={1,2,3}
)*C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
I need to keep track of maintenance on certain machines and how much
it
costs.
Would like to be able to show how much was spent on each machine in a
given
month or qtr.

any Ideas

Tks







--

Dave Peterson

sumif
 

Tks Dave greatly appreciate it.this does work now
Thank you again to all

"Dave Peterson" wrote:

Watch those parentheses!

=SUMPRODUCT((YEAR(A1:A10)=2005)*(MONTH(A1:A10)=11) *(C1:C10="H-540")*E1:E10))
you had:
=SUMPRODUCT((YEAR(A1:A10)=2005*(MONTH(A1:A10)=11)* (C1:C10="H-540")*E1:E10))

One of these things is not like the other <vbg.

And one more way:

=SUMPRODUCT(--(TEXT(A1:A10,"yyyymm")="200511"),--(C1:C10="H-540"),(E1:E10))

=sumproduct() likes to work with numbers. The -- stuff converts true's to 1's
and false's to 0's.




hellZg8 wrote:

this is the formula I have right know (in a seperate workbook)
=SUMPRODUCT((YEAR(A1:A10)=2005*(MONTH(A1:A10)=11)* (C1:C10="H-540")*E1:E10))
this gives me a $0.00 total when I should be at a $3500.00 total
The format for first column I had the month in was general , I tried typing
in the number for the month and the name same result.
I switch the format to Month-Day-Year

Ctrl+Shift+Enter ?? after didn't work either

"Bob Phillips" wrote:

The problem with having just a separate month column is that there can be
data for say Jan 2005 and Jan 2006, so I added a test for the year.

This should still get you the cost for Jan 2005

=SUMPRODUCT(($A$1:$A$100="Machine
#1")*(YEAR($C$1:$C$100)=2005)*(MONTH($C$1:$C$100)= 1)*$D$1:$D$100)

and this will get the first quarter

=SUMPRODUCT(($A$1:$A$100="Machine
#1")*(YEAR($C$1:$C$100)=2005)*(MONTH($C$1:$C$100)= {1,2,3})*$D$1:$D$100)

where column A holds the machine data, C holds the date, and D the amount.
Just change to suit


--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
Tks for the quick response.
The spreadsheet I'm using is set up like a database.I have 2 columns for
Date. The first only shows the month, the second shows the actual date the
main. was done.
The first column is the one I need (I think).

Here's my question
For the month of January for Machine #1 what was the total cost of
maintenance?

so for every Month in column1 that = January and for every machine #1 =
column 2
sum up all cost .

I'm not sure if it should be treated as an array (I've tried both ways
same
result)

Tks

"Bob Phillips" wrote:


=SUMPRODUCT((A1:A100="machine")*(YEAR(B1:B100)=200 5)*(MONTH(B1:B100)={1,2,3}
)*C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hellZg8" wrote in message
...
I need to keep track of maintenance on certain machines and how much
it
costs.
Would like to be able to show how much was spent on each machine in a
given
month or qtr.

any Ideas

Tks







--

Dave Peterson

sumif
 

Take a look at Pivot tables and you will see how easy this stuff is.

http://www.geocities.com/jonpeltier/...pivotstart.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"hellZg8" wrote in message
...
I need to keep track of maintenance on certain machines and how much it
costs.
Would like to be able to show how much was spent on each machine in a
given
month or qtr.

any Ideas

Tks