Conditional sum

[mushy]

Hi guys, first post...be gentle with me :P

Im trying to pick up sum values from a column based on certain conditions, i have come up with the following but its giving me a 0 value.

=SUM(IF('Data'!$C$2:$C$1144=01,IF('Data'!$D$2:$D$1144=18,IF('Data'!$E$2:$E$1144=3000,'Data'!$J$2:$J$1144,0),0)))

so, from the above, i need the following to be true from my selection criteria.

Column 1 - various values, but i need only those with a value of 1
Column 2 - various values, but i need only those with a value of 18
Column 1 - various values, but i need only those with a value of 3000
Sum Column 4 - Sum the results where the conditions are met with the above 3 columns

err help?

[Excelenator]

=SUM(IF('Data'!$C$2:$C$1144=01,IF('Data'!$D$2:$D$1 144=18,IF('Data'!$E$2:$E$1144=3000,'Data'!$J$2:$J$ 1144,0),0)))

Try this

Please Login or Register  to view this content.

[daddylonglegs]

Hello mushy,

your formula looks valid to me, as long as all the values in columns C, D, E and J are numeric it should work OK.

Formula needs to be confirmed with CTRL+SHIFT+ENTER....or perhaps you'd be better off with SUMPRODUCT, which will do the same thing without being array entered, i.e.

=SUMPRODUCT(--('Data'!$C$2:$C$1144=1),--('Data'!$D$2:$D$1144=18),--('Data'!$E$2:$E$1144=3000),'Data'!$J$2:$J$1144)

[mushy]

I'm rather new to some of the functions within excel, SUMPRODUCT worked fine.

Thank you for the replies and the help guys/gals, this now works fine.