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]

I would double check that. If they are different lengths you will get #Value. My ranges are defined as rows 1:10

Excel Workbook
	A	B	C	D
1	Found & Cleared	01/01/2011		2
2	Found & Cleared	21/01/2011
3	Found & Cleared	10/02/2011
4	Found & Cleared	02/03/2011
5	Found & Cleared	22/03/2011
6	Found & Cleared	11/04/2011
7	w	01/01/2011
8	d	01/01/2011
9	r	01/01/2011
10	t	01/01/2011
Sheet3

 

[Aladin Akyurek]

What do you get with:

=INDEX(month,1,1)

and

=ISNUMBER(INDEX(month,1,1))

 

[geospatial]

What do you get with:

=INDEX(month,1,1)

and

=ISNUMBER(INDEX(month,1,1))

for =INDEX(month,1,1) i get 40209.85417

for isnumber I get false

 

[Aladin Akyurek]

for =INDEX(month,1,1) i get 40209.85417

for isnumber I get false

Your date/time values appear to be text-numbers... Try to convert them into numbers.

 

[geospatial]

Your date/time values appear to be text-numbers... Try to convert them into numbers.

If i change them to numbers it just gives me the 40216 style number. If i try to convert that to a date it wont take effect unless I dobule click each individual cell.

 

[VoG]

Try selecting the dates, Data > Text to Columns, click Next twice, tick Date and select the date format (e.g. DMY) then click Finish

 

[geospatial]

I figured out the problem. My column Name says Month and when the sumproduct looks at that column it gets the #VALUE! error on that cell, then it reads fine the rest of the way. So I am guessing I need to set my dynamic range to start in row 2 and continue down.

 

[geospatial]

I figured out the problem. My column Name says Month and when the sumproduct looks at that column it gets the #VALUE! error on that cell, then it reads fine the rest of the way. So I am guessing I need to set my dynamic range to start in row 2 and continue down.

If I seperate the formula in two pieces they both work. For instance if I just do =sumproduct(--(category="Found & Cleared") it works and if i just do =sumproduct(--(MONTH(month)=1) it works, but when I do the =sumproduct(--category="Found/Cleared"),--(MONTH(month)=1) its back to the #VALUE error. So i am guessing it has to probably be a formatting issue? Do the columns have to be formatted the same?

 

[VoG]

No, the ranges have to be the same length as stated before. different lengths return exactly that error.

 

[geospatial]

No, the ranges have to be the same length as stated before. different lengths return exactly that error.

You were right. Once I figured out my value error was originnaly caused by the row 1 cell having a title, I changed the dynamic range for that column to start in row 2. Didnt change that in the other column I was looking at so it was still starting in row 1.

Thanks for all the help from everyone.