Using SUMPRODUCT function with date ranges

  • Thread starter Thread starter montana
  • Start date Start date
M

montana

I learned to use the SUMPRODUCT function through this forum and I need
little help. I have a column (column G) of four letter codes "SBIB".
In column Q the user enters a date of completion mm/dd/yyyy. I a
trying to put together a table that has my codes in Column V and i
columns W - AH are the months Jan - Dec.

In cell V2 is "SBIB"

In cell W2 i want to count the numbers of times SBIB occurs in column
and the Date entered in column Q is less than or equal to 01/31/2004.
I can accomplish this but I run into a problem with my formula for cel
X2. Here I want to count the number of times "SBIB" occurs in column
and the Date in column Q is greater than 01/31/2004 and less than o
equal 02/29/2004.

my formula for cell W2 is as follows
=SUMPRODUCT(($G$2:$G$1000=$V2)*($Q2:$Q1000<=38017))

My problem is how do create a range for cell X2 where I need to coun
only those occurences where the date is greater than 01/31/2004 an
less than 02/29/2004?

I hope this makes sense.

Thanks in advance for any help I do recieve on this
 
One way

=SUMPRODUCT(--($G$2:$G$1000=$V2),--($Q2:$Q1000<=DATE(2004,2,29)),--($Q2:$Q10
00>=DATE(2004,1,31)))
 
Back
Top