Sumproduct with text and date conditions, Excel 2010

[Dking131]

Hi,

I am stuck trying to use a sumproduct with three conditions.

My objective is to count the number of dates for a specific geographical region which fall within a certian range.


'Launch Tracker'!$H$3:$H$1200 = the column reference where the region is contained e.g. Europe

The date range is greater than 7 days from today for which I have a cell reference B1, and less than todays date (cell reference B2) +30 days

'Launch Tracker'!$AB$3:$AB$1223 is the column where the dates are contained


The current formula I am using is: =SUMPRODUCT(('Launch Tracker'!$H$3:$H$1200="Europe")*('Launch Tracker'!$AB$3:$AB$1223>$B$1)*('Launch Tracker'!$AB$3:$AB$1223<$B$2+30))

The value being returned is: #N/A

I have also tried: =SUMPRODUCT(--('Launch Tracker'!$H$3:$H$1200="Europe"),--('Launch Tracker'!$AB$3:$AB$1223>$B$1),--('Launch Tracker'!$AB$3:$AB$1223<$B$2+30))

Which returns: #VALUE!


Any suggestions greatly appreciated?

 

[VoG]

Try

=SUMPRODUCT(--('Launch Tracker'!$H$3:$H$1223="Europe"),--('Launch Tracker'!$AB$3:$AB$1223>$B$1),--('Launch Tracker'!$AB$3:$AB$1223<$B$2+30))

 

[Dking131]

This worked!
So I understand for future do all referenced Column lengths need to be the same?

Many Thanks for your help!

 

[VoG]

Yes, the ranges must be identical in size.

 

[Robert Mika]

As you are using Excel 2010:
=COUNTIFS('launch tracker'!$H$3:$H$1223,"Europe",'launch tracker'!$AB$3:$AB$1223,">"&$B$1,'launch tracker'!$AB$3:$AB$1223,"<"&$B$2+30)