Excel 2007 : If Then Where. Formula across worksheets

[antispam246]

Hi, first post

I've been trying to figure this out for awhile which lead me to these forums. Once I get the jist of how this works, it should help me with a great deal more.

I have a single workbook which contains several worksheets. I have a separate worksheet for formulas which create various totals based on the other worksheets. What I'm trying to do is create a formula like so

IF the date of cell in column D is equal to the year 2000 (the date format is day/month/year, and the day date is irrelvant in the formula) THEN give me the SUM of column F (but only where column F is in the same row as the column D that meets the required date).

I understand how to reference to another worksheet to create the formula just not the actually formula required to get the sum of a certain date.

Basically, I have a worksheet that records expenses over the past 8 years where each row is a purchase and includes data such as the date and amount etc.. so I want to create formulas on a separate worksheet that show the total expenditure for each year.

Any help appreciated. I will begin to crawl through these forums as they seem to contain a wealth of helpful stuff.

[martindwilson]

try

Please Login or Register  to view this content.

this would do 2008
adjust ranges and sheets to suit

[antispam246]

try

Please Login or Register  to view this content.

this would do 2008
adjust ranges and sheets to suit

It gives me a # Error. Only explanation is that wrong type of argument or operand is used.

Is it trying to SUM the date column to?

[darkyam]

The error is occurring with Datevalue for me. This should work:

Please Login or Register  to view this content.

This is also for 2008.

[Bob Phillips]

One of th eperils of using a non-standard date format

=SUMPRODUCT(--(D1:D200>=--"2008-01-01"),--(D1:D200<--"2009-01-01"),F1:F200)

[antispam246]

One of th eperils of using a non-standard date format

=SUMPRODUCT(--(D1:D200>=--"2008-01-01"),--(D1:D200<--"2009-01-01"),F1:F200)

Thank you, got it working perfectly with a slight variation of this.

If I wanted to exclude a specific cell or cells from this formula what would I add?

[martindwilson]

exclude what? something in the range D1:D200 ? under what criteria?

[antispam246]

exclude what? something in the range D1:D200 ? under what criteria?

Yes, the former, there's no specific criteria other than the cell itself or cells. So say I wanted to exclude D5, D7 and D101 etc..

[martindwilson]

cant see how to do that short of chnging those cells somehow, add a character or something

[antispam246]

Thanks anyway. I'll probably attach a keyword and go down that route.

Going back to my original question, how would I do the exact same formula but instead of basing it on dates, base it on a specific word.

For example, I wanted it too look at all rows where the column name = "Jon" and then total the equivalent value (currency £). Basically the same as above but using "Jon" instead of a range of dates.

Also, would I have to format the NAME cell to a specific type, is it better using general or text?

Many thanks