Prev: formating of text file
Next: Automatically update a number when printed
From: steve_m on 30 Apr 2010 04:51
I've got a list of dates dd/mm/yyyy in column A and a list of the numbers in
column B which is related to the number of occurrences of something on each
date listed. There are some gaps in the dates ie. it is not a continuous
calendar list.

What I need to do is count the total number of occurrences in April 2010 for
example. I've tried using SUMPRODUCT but I'm stuck! I've managed to count the
number of times a date in April 2010 is listed but not the sum of number of
occurrences in April 2010 from the column B? Hopefully that is semi-clear?!!
From: Ms-Exl-Learner on 30 Apr 2010 05:16
Try this…

=SUMPRODUCT((A1:A100>=DATE(2010,4,1))*(A1:A100<=DATE(2010,4,30)),(B1:B100))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"steve_m" wrote:

> I've got a list of dates dd/mm/yyyy in column A and a list of the numbers in
> column B which is related to the number of occurrences of something on each
> date listed. There are some gaps in the dates ie. it is not a continuous
> calendar list.
>
> What I need to do is count the total number of occurrences in April 2010 for
> example. I've tried using SUMPRODUCT but I'm stuck! I've managed to count the
> number of times a date in April 2010 is listed but not the sum of number of
> occurrences in April 2010 from the column B? Hopefully that is semi-clear?!!
From: Jacob Skaria on 30 Apr 2010 05:33
Try SUMPRODUCT() with TEXT()

=SUMPRODUCT((TEXT(A1:A100,"mmmyyyy")="Apr2010")*B1:B100)

--
Jacob (MVP - Excel)


"steve_m" wrote:

> I've got a list of dates dd/mm/yyyy in column A and a list of the numbers in
> column B which is related to the number of occurrences of something on each
> date listed. There are some gaps in the dates ie. it is not a continuous
> calendar list.
>
> What I need to do is count the total number of occurrences in April 2010 for
> example. I've tried using SUMPRODUCT but I'm stuck! I've managed to count the
> number of times a date in April 2010 is listed but not the sum of number of
> occurrences in April 2010 from the column B? Hopefully that is semi-clear?!!
From: steve_m on 30 Apr 2010 09:57
Thanks, that does the job! How could I modify this to only sum items in
column B if both the month in column A matches *and* a boolean value in
column C is true?

"Jacob Skaria" wrote:

> Try SUMPRODUCT() with TEXT()
>
> =SUMPRODUCT((TEXT(A1:A100,"mmmyyyy")="Apr2010")*B1:B100)
>
> --
> Jacob (MVP - Excel)
>
>
> "steve_m" wrote:
>
> > I've got a list of dates dd/mm/yyyy in column A and a list of the numbers in
> > column B which is related to the number of occurrences of something on each
> > date listed. There are some gaps in the dates ie. it is not a continuous
> > calendar list.
> >
> > What I need to do is count the total number of occurrences in April 2010 for
> > example. I've tried using SUMPRODUCT but I'm stuck! I've managed to count the
> > number of times a date in April 2010 is listed but not the sum of number of
> > occurrences in April 2010 from the column B? Hopefully that is semi-clear?!!
From: Fred Smith on 30 Apr 2010 10:23
Just add it as another condition. Assuming column C really has True/False
values in it, use:
=SUMPRODUCT((TEXT(A1:A100,"mmmyyyy")="Apr2010")*B1:B100*C1:C100)

Regards,
Fred

"steve_m" <stevem(a)discussions.microsoft.com> wrote in message
news:678C0BC0-FDEB-4A60-8400-B4DC882FFE2E(a)microsoft.com...
> Thanks, that does the job! How could I modify this to only sum items in
> column B if both the month in column A matches *and* a boolean value in
> column C is true?
>
> "Jacob Skaria" wrote:
>
>> Try SUMPRODUCT() with TEXT()
>>
>> =SUMPRODUCT((TEXT(A1:A100,"mmmyyyy")="Apr2010")*B1:B100)
>>
>> --
>> Jacob (MVP - Excel)
>>
>>
>> "steve_m" wrote:
>>
>> > I've got a list of dates dd/mm/yyyy in column A and a list of the
>> > numbers in
>> > column B which is related to the number of occurrences of something on
>> > each
>> > date listed. There are some gaps in the dates ie. it is not a
>> > continuous
>> > calendar list.
>> >
>> > What I need to do is count the total number of occurrences in April
>> > 2010 for
>> > example. I've tried using SUMPRODUCT but I'm stuck! I've managed to
>> > count the
>> > number of times a date in April 2010 is listed but not the sum of
>> > number of
>> > occurrences in April 2010 from the column B? Hopefully that is
>> > semi-clear?!!

 | 
Pages: 1
Prev: formating of text file
Next: Automatically update a number when printed