Need formula based on criteria.

  • Thread starter Thread starter Me
  • Start date Start date
M

Me

I have a spreadsheet listed by day as follows. I need the
data from column B, C & D summarized by month. How do I do
that?

Col A Col B Col C Col D
01/01/04 5 5 4
02/01/04 6 6 6
03/03/04 7 0 0

I need the result to look like this -
Jan 04 11 11 10
Mar 04 7 0 0

How do I tell it to search for dates between 01/01/04 -
31/01/04 in column A then add column B, C or D to comue up
with my totals for Jan 04?
 
Hi

one way

for Jan Col B use the following formula
=SUMPRODUCT(--(MONTH($A$2:$A$4)=1),B$2:B$4)

this can then be filled across for col C & D
for Feb
use
=SUMPRODUCT(--(MONTH($A$2:$A$4)=2),B$2:B$4)

Etc

alternatively, check out pivot tables - Debra Dalgleish has some good
instructions on her website at www.contextures.com/tiptech.html

Cheers
JulieD
 
Thank you. But I forgot to mention that my data covers
more than 1 year. How do I incorporate the year into my
formula, i.e. if I need to calculate Jan 2003 and also Jan
2004 in another total?
 
Me said:
I have a spreadsheet listed by day as follows. I need the
data from column B, C & D summarized by month. How do I do
that?

Col A Col B Col C Col D
01/01/04 5 5 4
02/01/04 6 6 6
03/03/04 7 0 0

I need the result to look like this -
Jan 04 11 11 10
Mar 04 7 0 0

How do I tell it to search for dates between 01/01/04 -
31/01/04 in column A then add column B, C or D to comue up
with my totals for Jan 04?
Click to expand...
If you format the date column to the form Jan-04 then you can do
Data-Subtotals on each change in the month, or use Autofilter.

--
Registered Linux User no 240308
Just waiting for Broadband to complete the conversion!(3 weeks and
counting!)
gordonATgbpcomputingDOTcoDOTuk
to email me remove the obvious!
 
Hi

the formula can be edited to include year, e.g.

=SUMPRODUCT(--(MONTH($A$2:$A$4)=1),--(YEAR($A$2:$A$4)=2004),B$2:B$4)

but i would investigate pivot tables as it would save a lot of typing

Cheers
JulieD
 
Various ways of skinning that cat.

1) SUMPRODUCT Formulas
2) PIVOT Table
3) DATA SUBTOTALS

In this instance I would probably go with 3

Assuming your data starts in Col A with all your dates in ColA and your data
also sorted on ColA.
Insert a new ColA before A, such that it becomes your new Col A.
With your dates now starting in say B2, in A2 put
=YEAR(B2)&TEXT(MONTH(B2),"00") and copy down as far as your data goes.
Select all the data, including Col A and do Data / Subtotals, At each change
in Col A, Sum (Now tick all the boxes you can see)

This allows you to show all the data, or just the summarised data
 
Back
Top