Sumproduct

[Jeff Podgorski]

I've tried and tried to make this work but each time it returns 0, please help.

=SUMPRODUCT(--(Data!M2:M15000=245),--(Data!N2:N15000=70110101),Data!O2:O15000)

Thanks

 

[pgc01]

Hi Jeff

The syntax seems ok, so it seems it would be a type problem. Maybe the values in one or several columns in M:O are text and you are treating them as number.

To test if the values are text, use for ex.:

=ISTEXT(Data!N2)

Please try.

 

[T. Valko]

I've tried and tried to make this work but each time it returns 0, please help.

=SUMPRODUCT(--(Data!M2:M15000=245),--(Data!N2:N15000=70110101),Data!O2:O15000)

Thanks

You probably have a data type mismatch.

See this. It describes common data type problems.

http://contextures.com/xlFunctions02.html#Trouble

 

[Jeff Podgorski]

This is a strange one, none of the cells are formatted as text and when I perform simple functions as =Data!M2 or N2 and so on, all of the correct values appear.

You probably have a data type mismatch.

See this. It describes common data type problems.

http://contextures.com/xlFunctions02.html#Trouble

 

[Scott Huish]

What are the results of the following formulas?

=SUMPRODUCT(--(ISTEXT(Data!M2:M15000)))
=SUMPRODUCT(--(ISTEXT(Data!N2:N15000)))
=SUMPRODUCT(--(ISTEXT(Data!O2:O15000)))

 

[Jeff Podgorski]

M and O returned a zero and N returned the correct value.

What are the results of the following formulas?

=SUMPRODUCT(--(ISTEXT(Data!M2:M15000)))
=SUMPRODUCT(--(ISTEXT(Data!N2:N15000)))
=SUMPRODUCT(--(ISTEXT(Data!O2:O15000)))

 

[Scott Huish]

If column N returned 14999 then all the values in that column are text.

 

[Jeff Podgorski]

So....simply add ISTEXT for the column N array? I'm confused, the cells are formatted as numbers not text.

If column N returned 14999 then all the values in that column are text.

 

[T. Valko]

So....simply add ISTEXT for the column N array? I'm confused, the cells are formatted as numbers not text.

The quick and dirty fix would be:

=SUMPRODUCT(--(Data!M2:M15000=245),--(Data!N2:N15000="70110101"),Data!O2:O15000)

Do any of the entries in column N have leading zeros?

 

[Jeff Podgorski]

No leading zeros, out of the office now,will try in the morning.

Thanks

The quick and dirty fix would be:

=SUMPRODUCT(--(Data!M2:M15000=245),--(Data!N2:N15000="70110101"),Data!O2:O15000)

Do any of the entries in column N have leading zeros?