Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a list of data in excel with a date column. When I want to generate a report I typically create a new column in the data tab and type in a code in text format like "April08", then I put a hidden column on the report tab with that code and use a sumif formula to add up the quantities in the column matching the "April08" Is there an easier way to do this in Excel?

share|improve this question
    
>type in a code in text format like "April08". A bit confused. You type this once, or once for every entry that has xx/04/2008 in the date column (or 04/xx/2008 in USian) – mcalex Mar 14 '13 at 14:45
    
try looking into Access, there is the ability to have "one-click" reports generated in nice formats. Might be a bit more complex than what you were looking for, but I think you'll find Access has a lot of reporting possibilities. – SnakeDoc Mar 14 '13 at 14:56
up vote 1 down vote accepted

Using the SUMIFS function, you can specify multiple criteria. So

=SUMIFS(data!B:B, data!A:A, ">="&startDate, data!A:A, "<="&endDate)

where column B in the data worksheet contains the data you want to sum, and column A contains the dates. startDate and endDate are named ranges with your start and end dates (in this example they would contain 1 Apr 2008 and 30 April 2008 respectively). Obviously you don't have to use named ranges; I've just put that in for clarity.

Alternatively, use a 'helper' column as you have, but put a formula in it instead of manually typing "April08" etc. Something like =DATE(YEAR(A1),MONTH(A1),1) would do the job.

share|improve this answer
    
Thank you this is perfect. – Richard Pullman Sep 5 '13 at 14:47

You didn't specify what version you are using or exactly how your data is laid out, but using a pivot table is a powerful tool to present the information how you want from the list of data.

If you aren't familiar with pivot tables, here is an excellent tutorial - Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one.

share|improve this answer

You can use array formulas to get the sum you want without having to add a helper column, like so:

=SUM((MONTH(dates)=4)*(YEAR(dates)=2008)*sales)

(see formula A in my example below)

Where:

dates -- the range that contains your dates (column A in my example below)
sales -- the range that contains the stuff you want to add up (column B in my example below)
4 and 2008 refers to my target month and year, in this case, anything that falls within April 2008.

Example:

enter image description here

This formula should also give you the same result (formula B above):

=SUM((TEXT(dates,"mmm yyyy")="Apr 2008")*sales)

Just change it to suit your spreadsheet.

Don't forget to commit the formulas using Ctrl + Shift + Enter.

share|improve this answer
    
Wow that is incredibly useful- I can't believe I've been wasting so much time the way I've been doing things. – Richard Pullman Mar 20 '13 at 20:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .