Tracking data by month
 

Hi,

I wonder if someone could help. Im creating a worksheet for our department
to track, among other things, profitability.

I have a separate worksheet for reports. This pulls together data from
several other worksheets, each of which contains data on one specific
project.

One report has to track the monthly profitability of a particular category
of billable job. So what I need is a formula that says:

€œLook at the deadline column of the worksheet called Ad hoc. For all cells
with a date in January 2008 (for instance), calculate the sum of the
corresponding cells in the previous column (the one that tracks our fees)€�,

Can anyone tell me how to go about this?

Thanks

Karl

Tracking data by month
 

=SUMPRODUCT(--(TEXT('Ad hoc'B2:B2000,"mmmyyyy")="Jan2008"),'Ad
hoc'!A2:A2000)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karl" wrote in message
...
Hi,

I wonder if someone could help. I'm creating a worksheet for our
department
to track, among other things, profitability.

I have a separate worksheet for reports. This pulls together data from
several other worksheets, each of which contains data on one specific
project.

One report has to track the monthly profitability of a particular category
of billable job. So what I need is a formula that says:

"Look at the deadline column of the worksheet called Ad hoc. For all cells
with a date in January 2008 (for instance), calculate the sum of the
corresponding cells in the previous column (the one that tracks our fees)",

Can anyone tell me how to go about this?

Thanks

Karl

Tracking data by month
 

Hi Bob,

Thanks for the help. I've tried to apply your formula as follows:

=SUMPRODUCT(--(TEXT('Ad hoc'!G9:G74,"mmmyyyy")="Jan2008"),'Ad hoc'!L9:L74)

The column 'G' contains the dates and the column 'L' the money.

Excel keeps telling me I have an error in the formula. Do you have any idea
what I'm doing wrong?

Does it make a difference what date format I use in column 'G'?

Thanks again

Karl

"Bob Phillips" wrote:

=SUMPRODUCT(--(TEXT('Ad hoc'B2:B2000,"mmmyyyy")="Jan2008"),'Ad
hoc'!A2:A2000)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karl" wrote in message
...
Hi,

I wonder if someone could help. I'm creating a worksheet for our
department
to track, among other things, profitability.

I have a separate worksheet for reports. This pulls together data from
several other worksheets, each of which contains data on one specific
project.

One report has to track the monthly profitability of a particular category
of billable job. So what I need is a formula that says:

"Look at the deadline column of the worksheet called Ad hoc. For all cells
with a date in January 2008 (for instance), calculate the sum of the
corresponding cells in the previous column (the one that tracks our fees)",

Can anyone tell me how to go about this?

Thanks

Karl

Tracking data by month
 

I have tested it here and with my data it works fine, so it is hard to say.

Is the error happening when you enter the formula, or as a result of the
formula.

Can you tell us what is in say G9:G12 and L9:L12?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karl" wrote in message
...
Hi Bob,

Thanks for the help. I've tried to apply your formula as follows:

=SUMPRODUCT(--(TEXT('Ad hoc'!G9:G74,"mmmyyyy")="Jan2008"),'Ad hoc'!L9:L74)

The column 'G' contains the dates and the column 'L' the money.

Excel keeps telling me I have an error in the formula. Do you have any
idea
what I'm doing wrong?

Does it make a difference what date format I use in column 'G'?

Thanks again

Karl

"Bob Phillips" wrote:

=SUMPRODUCT(--(TEXT('Ad hoc'B2:B2000,"mmmyyyy")="Jan2008"),'Ad
hoc'!A2:A2000)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Karl" wrote in message
...
Hi,

I wonder if someone could help. I'm creating a worksheet for our
department
to track, among other things, profitability.

I have a separate worksheet for reports. This pulls together data from
several other worksheets, each of which contains data on one specific
project.

One report has to track the monthly profitability of a particular
category
of billable job. So what I need is a formula that says:

"Look at the deadline column of the worksheet called Ad hoc. For all
cells
with a date in January 2008 (for instance), calculate the sum of the
corresponding cells in the previous column (the one that tracks our
fees)",

Can anyone tell me how to go about this?

Thanks

Karl

Tracking data by month
 

Maybe it's the list/argument separator.

Most people in the USA use a comma. Most people in Europe, use a semicolon.

=SUMPRODUCT(--(TEXT('Ad hoc'!G9:G74;"mmmyyyy")="Jan2008");'Ad hoc'!L9:L74)

If this doesn't help, what language do you use and what separator do you use?

Could be a difference in the function names, too.


Karl wrote:

Hi Bob,

Thanks for the help. I've tried to apply your formula as follows:

=SUMPRODUCT(--(TEXT('Ad hoc'!G9:G74,"mmmyyyy")="Jan2008"),'Ad hoc'!L9:L74)

The column 'G' contains the dates and the column 'L' the money.

Excel keeps telling me I have an error in the formula. Do you have any idea
what I'm doing wrong?

Does it make a difference what date format I use in column 'G'?

Thanks again

Karl

"Bob Phillips" wrote:

=SUMPRODUCT(--(TEXT('Ad hoc'B2:B2000,"mmmyyyy")="Jan2008"),'Ad
hoc'!A2:A2000)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karl" wrote in message
...
Hi,

I wonder if someone could help. I'm creating a worksheet for our
department
to track, among other things, profitability.

I have a separate worksheet for reports. This pulls together data from
several other worksheets, each of which contains data on one specific
project.

One report has to track the monthly profitability of a particular category
of billable job. So what I need is a formula that says:

"Look at the deadline column of the worksheet called Ad hoc. For all cells
with a date in January 2008 (for instance), calculate the sum of the
corresponding cells in the previous column (the one that tracks our fees)",

Can anyone tell me how to go about this?

Thanks

Karl





--

Dave Peterson