SumProduct Formula Giving Zero Value

[mazher]

Hi All,

I am using this formula

=SUMPRODUCT(--(C2:C18396=Q3)*--(H2:H18396=R2)*((N2:N18396)))

in cell R2 but is giving zero

I have also tried using

=SUMPRODUCT(--(C2:C18396=Q3),--(H2:H18396="CASH"),--(N2:N18396))

but the same result

if some guru can tell me, where i am wrong.

Thnaks in advance.

Regards

Mazher

 

[VoG]

Try

=SUMPRODUCT(--(C2:C18396=Q3),--(H2:H18396="CASH"),N2:N18396)

 

[mazher]

Thanks VoG

I have also tried
=SUMPRODUCT(--(C2:C18396=Q3),--(H2:H18396="CASH"),N2:N18396)

but the same 0.00 is comming.

Regards

Mazher

 

[scottylad2]

Thanks VoG

I have also tried
=SUMPRODUCT(--(C2:C18396=Q3),--(H2:H18396="CASH"),N2:N18396)

but the same 0.00 is comming.

Regards

Mazher

Is the data in Q3 and column C the same? are the words "CASH" idendical in the column ? are any of the cells resulting in errors in either column C or H?

 

[VoG]

What do

=COUNTIF(C2:C18396,Q3)

and

=COUNTIF(H2:H18396,"CASH")

give?

 

[Jonmo1]

Either none of the rows meet the 2 criteria (Q3 and CASH)
or
The numbers in column N are not really numbers.

What does this return?
=ISNUMBER(N2) <-filled down to N18396

 

[p45cal]

When debugging formulae like this, I find if you reduce the size of the ranges in the formula to where you know there should be a non-zero result, eg:
=SUMPRODUCT(--(C20:C30=Q3),--(H20:H30="CASH"),N20:N30)
then use Formula Audit and examine the formula being calculated step by step. This will quickly show where it's going wrong. Then expand the ranges to their original.

 

[mazher]

Hi Vog

=COUNTIF(C2:C18396,Q3) gives 0
and
=COUNTIF(H2:H18396,"CASH") gives 17833


Jonmo 1

=ISNUMBER(N2) when copied down till end gives true.

I think something wrong with the dates column

Regards

Mazher

 

[VoG]

There are no matches for Q3 in column C.

What do you have in Q3 and what's in column C?

 

[mazher]

Hi VoG

In Q3 its the date 01/08/2011

In column C there are also dates ranging from 01/08/2011 to 31/08/2011

Actually I have combined 31 cvs files and converted to one single file and the format in column was custom like this 01/08/2011 00:10:00

Might be this causing the problem.

Regards

Mazher