# Example 052 - Excel to Python¶

### How to calculate SUM if cells not equal to something ?¶

#### =SUMIFS($E$5:$E$29,$D$5:$D$29,H5)¶

In [1]:
import pandas as pd

#-----------------------------------------------
#-----------------------------------------------


In [2]:
#-----------------------------------------------
# print the dataset
#-----------------------------------------------
print(); print(df.columns)
print(); print(df.shape)
print(); print(df.tail(5))

print(); print("Print the dataset: ")
print(); print(df)

Index(['Date', 'Order', 'Item', 'Value'], dtype='object')

(25, 4)

Date   Order    Item  Value
0   5/04/2009  100100     RED    100
1   6/11/2012  100101    BLUE    125
2  31/10/2010  100102  YELLOW    150
3   6/09/2011  100103   GREEN    175
4   6/11/2012  100104   WHITE    200

Date   Order   Item  Value
20   6/11/2012  100120  GREEN    300
21   6/11/2012  100121  WHITE    125
22  31/10/2010  100122  GREEN    175
23   5/04/2009  100123  WHITE    200
24   6/09/2011  100124   BLUE    225

Print the dataset:

Date   Order    Item  Value
0    5/04/2009  100100     RED    100
1    6/11/2012  100101    BLUE    125
2   31/10/2010  100102  YELLOW    150
3    6/09/2011  100103   GREEN    175
4    6/11/2012  100104   WHITE    200
5   31/10/2010  100105    BLUE    225
6    5/04/2009  100106  YELLOW    250
7    6/11/2012  100107   GREEN    275
8   31/10/2010  100108     RED    300
9    6/09/2011  100109    BLUE    125
10   6/11/2012  100110   GREEN    150
11  31/10/2010  100111   WHITE    175
12   5/04/2009  100112    BLUE    200
13   6/09/2011  100113  YELLOW    225
14   6/11/2012  100114   GREEN    250
15  31/10/2010  100115     RED    175
16   5/04/2009  100116    BLUE    200
17   6/11/2012  100117   GREEN    225
18  31/10/2010  100118   WHITE    250
19   6/09/2011  100119    BLUE    275
20   6/11/2012  100120   GREEN    300
21   6/11/2012  100121   WHITE    125
22  31/10/2010  100122   GREEN    175
23   5/04/2009  100123   WHITE    200
24   6/09/2011  100124    BLUE    225

In [3]:
# --------------------------------------------------------------
# Calculate SUM if not equal to
# --------------------------------------------------------------

SUM_RED = SUM_not_RED = 0

for i in range(0, len(df)):
if df.iloc[i,2] == "RED":
SUM_RED = SUM_RED + df.iloc[i,3]
else: SUM_not_RED = SUM_not_RED + df.iloc[i,3]

ListObj = []
ListObj.append(["RED", SUM_RED])
ListObj.append(["not_RED", SUM_not_RED])

In [4]:
print(ListObj)
df2 = pd.DataFrame(ListObj, columns = ['Criteria', 'Sum'])

[['RED', 575], ['not_RED', 4500]]

In [5]:
print(df2)

  Criteria   Sum
0      RED   575
1  not_RED  4500

In [ ]:



# Equivalent codes in R Programming language¶

In [ ]: