Extract and Filter by year and/or region

[workingtolearn]

Hi all,

Here's what I'm trying to do: display a graph for dollar totals based on year (with date entered as xx/xx/xx). So, in the below example I'd like to get the sum of all totals for 2009 and separately, 2010.

I'd also like to group states into regions and then get totals per region (i.e. NJ = "East", CA = "West" and then get separate sum totals per region).

Date Name State Total Reason
1/5/2009 Roger NJ 5 Sale
1/6/2009 Roger NJ 2 Sale
2/8/2009 Mike NJ 4 Sale
4/5/2009 Dean DE 3 Sale
4/5/2010 Roger NJ 12 Return
6/5/2010 Dean DE 15 Retun
7/8/2010 Mike NJ 17 Sale
9/8/2010 Mike NJ 22 Return
9/15/2010 Dean DE 0 Return
1/20/2010 Susie CA 55

I've been searching for help, and going a little bonkers right now...I'm new to all of this, and I think my two issues below are a little advanced for where I'm at right now (I'm at IF, and basic COUNTIF functions - anything beyond that is greek to me).

One problem, I need to have formulas for both Excel and Google Spreadsheets, so autofilter and pivot tables are out of the question.

I'd really appreciate any help.

Many, many, many thanks!

[JBeaucaire]

Here are some suggestions using SUMPRODUCT() constructs.


As you expand these into your own data set, resist the urge to try and set entire columns as your range.