Tracking data by month

[Karl]

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

 

[Bob Phillips]

=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]

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]

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)

 

[Dave Peterson]

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.