I am trying to add a column (col5) to the DataFrame such as below where the value in `col5` needs to be a value from `col4` that satisfies certain conditions in another column at the same row. For example, at row 1 in `col5`, I wanted a value from `col4` such that `col1 and col2` has a value the same as row 1 but the value in `col3` is != value in row1. In excel this can be done using sumifs as shown in the image. Any help is appreciated.
![Image shown in the link below](https://i.stack.imgur.com/K47vw.png)
I have updated my question based on the answer from Paul.

```
df=pd.DataFrame({"col1":[1,1,1,1,2,2,2,2], "col2":['a','a','b', 'b','c', 'c', 'd', 'd'], "col3":['p','q','p', 'q', 'p','q','p', 'q'], 'col4':[100,200,300,400,500,600,700,800]})

```

What I want to accomplish is something like below to add a `col5` which checks conditions on other columns where `col1` and `col2` should be the same but `col3` should not match. Assuming that `col3` will have only two different values so saying `col3` to not match means `col3` should have another value.

```
df2 = df

df['col5'] = df[(df.col1 == df2.col1) & (df.col2 == df2.col2) & (df.col3 != df2.col3)].col4

df

>>>

  col1 col2 col3 col4   col5
0   1   a    p   100    NaN
1   1   a    q   200    NaN
2   1   b    p   300    NaN
3   1   b    q   400    NaN
4   2   c    p   500    NaN
5   2   c    q   600    NaN
6   2   d    p   700    NaN
7   2   d    q   800    NaN

```

When I run this I get all NaN in `col5` as shown above.

What I want to get is as below. Here the arrangement seems to make is simple like getting from the next or previous row but in the extended data, it can be at any row.

```
>>>

  col1 col2 col3 col4   col5
0   1   a    p   100    200
1   1   a    q   200    100
2   1   b    p   300    400
3   1   b    q   400    300
4   2   c    p   500    600
5   2   c    q   600    500
6   2   d    p   700    800
7   2   d    q   800    700

```