sumproduct issue

[gmazza76]

Good Afternoon,

I am trying to get a sumproduct to work as below

=IF($B14="","",SUMPRODUCT((SRDate>=$E$8)*(SRDate<$E$9)*(AgentComp=B14)*(Questions=D$13)))

The above record 0, but when i remove the First part ( referencing a column with the date in ) it works as follows collating the correct data.

=IF($B14="","",SUMPRODUCT((AgentComp=B14)*(Questions=D$13)))

I have used the following offset for the criteria for SRDate

=OFFSET(Completed!$B$3,0,0,COUNTA(Completed!$A:$A)-1,1)

Is there anyway i can get it to incorperate the date part as this is very important to the report i am trying to create.

Many Thanks
Gavin

 

[Brian from Maui]

SRDate is column A?

Do you have empty cells in column A?

 

[rafaelspeixoto]

Sumproduct only works with ranges with the same size, so make sure SRDate, AgentComp and Questions have the same size.

 

[gmazza76]

Column A has a formula to show the month as a number so when a date is intered column A is populated

SRdate is in column B.

 

[gmazza76]

Sorry what do you mean the same size?
Each of the columns hold specific data.
SRDate is the date an enquiry was raised
AgentComp holds the name of the person who raised the enquiry
Question holds 1 of 6 options the question can be based around

Hope this makes sense
Gavin

 

[barry houdini]

Hello Gavin,

I suggest you try the formula with cell references instead of named ranges - if that works then the problem is probably with your named ranges - if it still doesn't then the problem lies elsewhere

Aloha Brian!

 

[rafaelspeixoto]

SRDate is a worksheet range, defined as =OFFSET(Completed!$B$3,0,0,COUNTA(Completed!$A:$A)-1,1)
so it is one column with several --> COUNTA(Completed!$A:$A)-1 <-- rows.

For ex, SRDate could be a range with one column and 20 rows (so a 20x1 array size), like Completed!$B$3:$B$22

If the other 2 parameters have a different size (i.e. if any one of them is not a 20x1 arrays in this case) sumproduct will not work. How are the AgentComp and Questions ranges defined?

 

[gmazza76]

I have just checked the cell format where the date is held "column A"

Some of the cells in general show as 40788 under General ( Not as a date format ) When i try to make all of them the same format some of the dates change to numbers as above but some stay as a date.

When i change the cell formatt the sumproduct seems to work

Is there anyway I can amend this?

Many Thanks

 

[barry houdini]

Try using "text to columns" to convert all those "dates" to real dates

select date column > Data > Text to columns > Finish

 

[rafaelspeixoto]

Some of those cells might be formatted as text. Check the cell type for them:
=TYPE(A1)

if the type =1 it is ok. If type =2 you have text instead of dates (so they will not change when you change the cell format)