Need formula based on criteria.

[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?

 

[JulieD]

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

 

[Me]

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?

 

[Gordon]

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?

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!

 

[JulieD]

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

 

[Ken Wright]

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