Sumproduct - four variables

**jcastellano** · 08-31-2007, 08:09 AM

I am trying to sort through some data with four variables. I thought a sumproduct would work, but it doesn't seem to be working. Here is the formula. Any suggestions?

SUMPRODUCT(--($L$9:$L$3038="Permanent"),--($E$9:$E$3038="No"),--($G$9:$G$3038="No"),--($C$9:$C$3038=R1),$H$9:$H$3038)

**NBVC** · 08-31-2007, 08:11 AM

That formula looks fine....

what isn't working...wrong result or error message (which one?).

**jcastellano** · 09-05-2007, 10:58 AM

The response is not an error. It is producing the result of zero. Any idea why?

**NBVC** · 09-05-2007, 11:02 AM

That probably means exact matches are not being found....

make sure spelling is accurate and that some cells don't have extra spaces etc. I am not sure what is in R1, but also make sure that numbers are not stored as text, etc...

if you want to post a sample zipped file... I can look at it for you.

**Mark@Work** · 09-05-2007, 11:13 AM

Originally Posted by jcastellano

I am trying to sort through some data with four variables. I thought a sumproduct would work, but it doesn't seem to be working. Here is the formula. Any suggestions?

SUMPRODUCT(--($L$9:$L$3038="Permanent"),--($E$9:$E$3038="No"),--($G$9:$G$3038="No"),--($C$9:$C$3038=R1),$H$9:$H$3038)

I would suggest that there are NO rows where ALL the critera are SIMULTANEOUSLY met.

Have you spelt "Permanent" correctly in every case?
Is the TYPE of the variables in column C compatible with the TYPE of variable in R1? [--("1"=1) gives zero].
Have you got any trailing or leading blanks or other "non-printing" characters in the data?

Mark.