Sumproduct problems eith columns of different formats

[Knocknaboula]

HI All,

i have a problem with sumproducts not returning correct values


Col Format
Col C = text
Col E = general
Col I = Text


EQ Equity Sheet


Col C = Text
Col E = General
Col G = Text
Col Z = General


I want to match col c's, col e's I with G and return and sum the values in Col z.

=SUMPRODUCT(('EQ Equity'!$C$4:$C$5000=C4)*--('EQ Equity'!$G$4:$G$5000=I4)*--('EQ Equity'!$E$4:$E$5000=$E4),--('EQ Equity'!$Z$4:$Z$5000=$E4))

Can somebody please help me?

[Pete_UK]

Try it like this:

=SUMPRODUCT(('EQ Equity'!$C$4:$C$5000=C4)*('EQ Equity'!$G$4:$G$5000=I4)*('EQ Equity'!$E$4:$E$5000=$E4),'EQ Equity'!$Z$4:$Z$5000)

Hope this helps.

Pete

[Richard Buttrey]

Hi,

We need to see the problem in context. Would you upload your workbook.
What values are being returned? Wrong values, Error values or no values at all?

[Knocknaboula]

Thanks for this , i hadn't noticed the error in the last part. This just returns "0" instead of the value in column z .

[Pete_UK]

Perhaps the values in column Z are text values that just look like numbers. As Richard suggested, perhaps you can attach your workbook - the FAQ describes how to.

Hope this helps.

Pete

[daddylonglegs]

Perhaps test that you get a positive count for the criteria alone: what do you get with this?

=SUMPRODUCT(('EQ Equity'!$C$4:$C$5000=C4)*('EQ Equity'!$G$4:$G$5000=I4)*('EQ Equity'!$E$4:$E$5000=$E4))

[Knocknaboula]

Here you go

[Knocknaboula]

This also returns "0"

[Pete_UK]

You need a formula like this in AA4:

=IF(C4="","",SUMPRODUCT(('EQ Equity'!$C$4:$C$56=$C4)*('EQ Equity'!$E$4:$E$56=E4&"")*('EQ Equity'!$G$4:$G$56=I4),'EQ Equity'!$Z$4:$Z$56))

Note that column E values on the EQ Equity sheet are actually text values so you have to join "" onto E4 to get the same format. Also, you were comparing I4 (a name) to those values, so the third term should refer to column G.

However, this will still return zeros as column Z contains text values - you should convert these to proper numbers if you want to get a numerical result.

Hope this helps.

Pete

[Wormsek]

You can also use array formula:
=SUM(('EQ Equity'!$C$4:$C$100=C4)*('EQ Equity'!$G$4:$G$100=I4)*('EQ Equity'!$E$4:$E$100=TEXT($E4,"@"))*(IF(ISERROR(1*'EQ Equity'!$Z$4:$Z$100),0,(1*'EQ Equity'!$Z$4:$Z$100))))