Need formula based on criteria.

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


"Me" <[email protected]> wrote in message
news:[email protected]...
>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?

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?
>-----Original Message-----
>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" <[email protected]> wrote in
message
>news:[email protected]...
>>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?
>
>
>.
>

Me wrote:
> 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!

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


"Me" <[email protected]> wrote in message
news:[email protected]...
> 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?
>>-----Original Message-----
>>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" <[email protected]> wrote in
> message
>>news:[email protected]...
>>>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?
>>
>>
>>.
>>

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Me" <[email protected]> wrote in message
news:[email protected]...
> 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?