Strange error using SUMPRODUCT

[cyd44]

I am using SUMPRODUCT Function and have a strange error which suggests I am pointing to an empty cell (the empty cell pointed to does actually contain data though).


I am using the following function:-


=SUMPRODUCT(--$C$1:$C$500>=X46)--($C$1:$C$500<=Y46)--($A$1:$A$500=$R$61)


Where Column C containes dates for each record
X46 containes the 1st day of Jan (01/01/2012)
Y46 containes last day of Jan (31/01/2012)

Column A contains a type X,Y,Z for example
I have cell R61 which contains a unique value which has been obtained from column A.

I now want to count the number of records containing the Type in R61 by using the above function for each month.

The results i am getting are not correct. In Jan the result is 0 and excel is promting that I am pointing to an empty cell (points to C1 which does contain a date). The other months all contain 1 which is not correct either.

What is confusing me is that I am using the above function which works fine when counting other columns but for this one it does not want to work.

Am I doing something fundamentally wrong here?

 

[vbaInet]

What version of Excel are you working on?

 

[cyd44]

What version of Excel are you working on?

Excel 2010. Am getting some very strange results from this and also when using macros but this is another matter I will deal with once I have solved this particular problem

 

[vbaInet]

You can look into the COUNTIFS and SUMIFS() function. In your case CountIfs is what you need for this problem.

 

[Brianwarnock]

Vbainet is correct that you do not need to use sumproduct in 2010, it is a long time since I used it but the bracketing does not look right, cannot do a quick check as browsing on an IPad .

 

[vbaInet]

Vbainet is correct that you do not need to use sumproduct in 2010, it is a long time since I used it but the bracketing does not look right, cannot do a quick check as browsing on an IPad .

I didn't even notice that Brian... it certainly isn't right.

 

[NBVC]

The problem with the SUMPRODUCT() is that you need to have commas separating the arguments before the --..

e.g.

=SUMPRODUCT(--($C$1:$C$500>=X46),--($C$1:$C$500<=Y46),--($A$1:$A$500=$R$61))

but also, as mentioned you can use COUNTIFS

=COUNTIFS($C$1:$C$500,">="&X46, $C$1:$C$500,"<="&Y46,$A$1:$A$500,$R$61)