Sumproductif with dynamic ranges

[geospatial]

I was wondering if you can use the formual sumproductif with dynamic ranges. I currently have the formula =sumproductif(category="Found & Cleared")*(month="january") It keeps coming up with an #NAME error
The dynamic named ranges that I have are category which corresponds to Column C and Month with corresponds to Column D. When looking at the cell data under month it will say january but if you click on the cell it says the actual date such as 01/01/2010 08:00:00 PM. Not sure if that is messing up the formula or not.

 

[VoG]

It should be SUMPRODUCT not SUMPRODUCTIF.

 

[geospatial]

It should be SUMPRODUCT not SUMPRODUCTIF.

Thanks,

I will give it a shot. I had found the sumproductif from another forum.

 

[geospatial]

Changed formula to =sumproduct(category="Found & Cleared")*(month="january") and it did get rid of the #NAME error but everything shows up as 0. I know there should be 1300.

 

[VoG]

This works for me

=SUMPRODUCT(--(category="Found & Cleared"),--(month="january"))

 

[geospatial]

This works for me

=SUMPRODUCT(--(category="Found & Cleared"),--(month="january"))

With trying this I get the #VALUE! error. Im guessing because the month column is actually dates but show up as the month field. Would i need to change "january" to greater than jan 1 and less than feb 1?

 

[VoG]

Try

=SUMPRODUCT(--(category="Found & Cleared"),--(MONTH(month)=1))

 

[geospatial]

Try

=SUMPRODUCT(--(category="Found & Cleared"),--(MONTH(month)=1))

Im still getting the #value! error. Thinking it has got to do with the fact that category is text and month is not text.

 

[VoG]

You need to make sure that both ranges are the same length.

 

[geospatial]

they are both the same amount of rows and 1 column.