Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.