SUMPRODUCT help with dates
 

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

SUMPRODUCT help with dates
 

Try this€¦

=SUMPRODUCT((A1:A100=DATE(2010,4,1))*(A1:A100<=DA TE(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?!!

SUMPRODUCT help with dates
 

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

SUMPRODUCT help with dates
 

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

SUMPRODUCT help with dates
 

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" wrote in message
...
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?!!