Sum If On One Column With Multiple Values and One Other Column

[ir121973]

Hi, I wonder whether someone may be able to help me please,

I have a spreadsheet where I'm trying to use the SumIF function to sum a particular value which must fit certain criterias from other columns.

I have managed half of it, but I just can't seem to work out the rest. I would like to sum the figures in Column AE, but only if the values in Column J are either 'H', I' or 'P' and the value in Column K is 'G'.

This the formula I've put together to pull together the multiple values in Column J, but for the life of me can't find a way to incorporate the value from Column K aswell.

=Sum(SumIF($J$3:$J$200,{"H";"I";"P"},$AE$3:$AE$200)

Could someone perhaps give me a helping hand with this please.

Many thanks

Chris

 

[barry houdini]

Hello Chris,

which version of Excel are you using? In Excel 2007 and later you can use SUMIFS function (with an extra "S" at the end), i.e.

=SUM(SUMIFS($AE$3:$AE$200,$J$3:$J$200,{"H";"I";"P"},$K$3:$K$200,"G"))

or in any version you can use SUMPRODUCT

=SUMPRODUCT($AE$3:$AE$200,ISNUMBER(MATCH($J$3:$J$200,{"H";"I";"P"},0))*($K$3:$K$200="G"))

 

[ir121973]

Hi many thanks for your reply.

I'm using Excel 2003, so I'll have a try at the second formula and I'll let you know how I get on.

Kind Regards

Chris

 

[ir121973]

Hi,

Just thought I'd give you a very quick update. I've just tried this out and it works a treat.

Thank you so much.

Kind Regards

Chris