Using the 2 values from one column with Conditional Sum

[moppy_j]

Hi, i need help with a Conditional Sum. The below formula is working properly.

=SUM(IF($B$5:$B$198="ESM",IF($K$5:$K$198="China",IF($A$5:$A$198="Forecast",$P$5:$P$198,0),0),0))

Whereby Column B= ESM, Column K is China, Column A is Forecast and Column P is the data to Sum.

What i need to do now is a sum whereby instead of adding the China cells, I add Hong Kong and Taiwan.

So, i think my problem is that it's trying to add cells whereby the value in K is both Hong Kong AND Taiwan, when i need to add cells where Column K = Hong Kong OR Taiwan.

Any thoughts about how i can add the OR syntax into this formula?

Thanks

Chris

 

[Guanjin Peter]

have you tried sum product formula?

 

[moppy_j]

I am trying the sumproduct formula now but i'm not familiar with it.

So far i have managed this

=SUMPRODUCT(($A$4:$A$198="Forecast")*($B$4:$B$198="ESM")*($K$4:$K$198="Hong Kong")*($P$4:$P$198))

But it hasn't worked.

Any ideas?

For simplicity reasons i am trying to do it for Hong Kong only at first

Thanks

 

[moppy_j]

I have managed to make this work with sumproduct, thanks

 

[Guanjin Peter]

welcome.