Summing Values in column based upon multiple criteria

**shantanuk** · 09-21-2012, 08:35 AM

Hi,

Please refer to the attached excel file.

I want to sum all the values in range U5:U27 if the corresponding values in column D and column R are matching (e.g. D5=R5, D6=R6 etc.)

Thanks in advance for the help.

Thanks,

Shantanu

**benishiryo** · 09-21-2012, 08:40 AM

hi Shantanu, so you want to add to your formula a criteria that D & R must match? try:
=IF(AND(ISERROR(ROUNDDOWN(((S5-T5)/T5)*100,2)),D5<>R5),"",ROUNDDOWN(((S5-T5)/T5)*100,2))

**shantanuk** · 09-21-2012, 08:46 AM

Hi,

I am sorry if I was not clear earlier. What I want to acheive is the sum of all the values in U5:U26 and display them in D27. The criteria I want to use for picking up the value in column U would be for e.g. if D5=R5 then pick up U5 for sum and so on.

Hope I am clear this time

Thanks,

**benishiryo** · 09-21-2012, 09:23 AM

hmmmm do you mean D28 where your SUMIFS is? how about copying this & paste into the formula bar:
=SUMPRODUCT((D5:D27=R5:R27)*(D5:D27>0)*(IF(ISNUMBER(U5:U27),U5:U27,0)))

press CTRL + SHIFT + ENTER to confirm. so for this case, only D5 is equals to R5 & amount of 175 should be shown? and if D6 is equals to R6, it would be 175 + 300?