def sumifs(sum_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B
# WARNING:
# - The following is not currently implemented:
# The sum_range argument does not have to be the same size and shape as
# the range argument. The actual cells that are added are determined by
# using the upper leftmost cell in the sum_range argument as the
# beginning cell, and then including cells that correspond in size and
# shape to the range argument.
assert_list_like(sum_range)
assert len(args) and len(args) % 2 == 0, \
'Must have paired criteria and ranges'
# count the number of times a particular cell matches the criteria
index_counts = Counter(
it.chain.from_iterable(
find_corresponding_index(rng, criteria)
for rng, criteria in zip(args[0::2], args[1::2])))
ifs_count = len(args) // 2
max_idx = len(sum_range)
indices = tuple(idx for idx, cnt in index_counts.items()
if cnt == ifs_count and idx < max_idx)
return sum(_numerics(sum_range[idx] for idx in indices))
def countifs(*args): # Excel reference: https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
arg_list = list(args)
l = len(arg_list)
if l % 2 != 0:
raise Exception('excellib.countifs() must have a pair number of arguments, here %d' % l)
if l >= 2:
indexes = find_corresponding_index(args[0], args[1]) # find indexes that match first layer of countif
remaining_ranges = [elem for i, elem in enumerate(arg_list[2:]) if i % 2 == 0] # get only ranges
remaining_criteria = [elem for i, elem in enumerate(arg_list[2:]) if i % 2 == 1] # get only criteria
filtered_remaining_ranges = []
for range in remaining_ranges: # filter items in remaining_ranges that match valid indexes from first countif layer
filtered_remaining_range = []
for index, item in enumerate(range):
if index in indexes:
filtered_remaining_range.append(item)
filtered_remaining_ranges.append(filtered_remaining_range)
new_tuple = ()
for index, range in enumerate(filtered_remaining_ranges): # rebuild the tuple that will be the argument of next layer
new_tuple += (range, remaining_criteria[index])
return min(countifs(*new_tuple), len(indexes)) # only consider the minimum number across all layer responses
else:
return float('inf')
def countif(rng, criteria):
# Excel reference: https://support.office.com/en-us/article/
# COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
if not list_like(rng):
rng = ((rng, ), )
valid = find_corresponding_index(rng, criteria)
return len(valid)
def sumifs(sum_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B
assert_list_like(sum_range)
assert len(args) and len(args) % 2 == 0, \
'Must have paired criteria and ranges'
size = len(sum_range), len(sum_range[0])
for rng in args[0::2]:
assert size == (len(rng), len(rng[0])), \
"Size mismatch criteria range, sum range"
# count the number of times a particular cell matches the criteria
index_counts = Counter(
it.chain.from_iterable(
find_corresponding_index(rng, criteria)
for rng, criteria in zip(args[0::2], args[1::2])))
ifs_count = len(args) // 2
indices = tuple(idx for idx, cnt in index_counts.items()
if cnt == ifs_count)
return sum(
_numerics((sum_range[r][c] for r, c in indices), keep_bools=True))
def countif(rng, criteria):
"""
Use COUNTIF, one of the statistical functions, to count the number of cells
that meet a criterion; for example, to count the number of times a
particular city appears in a customer list.
In its simplest form, COUNTIF says:
=COUNTIF(Where do you want to look?, What do you want to look for?)
:param rng:
:param criteria:
:return:
.. reference::
https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
.. warning:
wildcards are not supported.
"""
valid = find_corresponding_index(rng, criteria)
return len(valid)
def sumif(
range,
criteria,
sum_range=[]
): # Excel reference: https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b
# WARNING:
# - wildcards not supported
# - doesn't really follow 2nd remark about sum_range length
if type(range) != list:
raise TypeError('%s must be a list' % str(range))
if type(sum_range) != list:
raise TypeError('%s must be a list' % str(sum_range))
if isinstance(criteria, list) and not isinstance(criteria,
(str, bool)): # ugly...
return 0
indexes = find_corresponding_index(range, criteria)
def f(x):
return sum_range[x] if x < len(sum_range) else 0
if len(sum_range) == 0:
return sum([range[x] for x in indexes])
else:
return sum(map(f, indexes))
def countif(range, criteria): # Excel reference: https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
# WARNING:
# - wildcards not supported
# - support of strings with >, <, <=, =>, <> not provided
valid = find_corresponding_index(range, criteria)
return len(valid)
def countif(range, criteria):
# Excel reference: https://support.office.com/en-us/article/
# COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
# WARNING:
# - wildcards not supported ::TODO:: test if this is no longer true
# - support of strings with >, <, <=, =>, <> not provided
valid = find_corresponding_index(range, criteria)
return len(valid)
def countifs(*args):
"""
The COUNTIFS function applies criteria to cells across multiple ranges and
counts the number of times all criteria are met.
:param args:
:return:
.. reference::
https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
"""
arg_list = list(args)
l = len(arg_list)
if l % 2 != 0:
raise Exception('excellib.countifs() must have a pair number of '
'arguments, here %d' % l)
if l >= 2:
# find indexes that match first layer of countif
indexes = find_corresponding_index(args[0], args[1])
# get only ranges
remaining_ranges = [elem for i, elem in enumerate(arg_list[2:]) if i % 2 == 0]
# get only criteria
remaining_criteria = [elem for i, elem in enumerate(arg_list[2:]) if i % 2 == 1]
filtered_remaining_ranges = []
# filter items in remaining_ranges that match valid indexes from first countif layer
for rng in remaining_ranges:
filtered_remaining_range = []
for idx, item in enumerate(rng):
if idx in indexes:
filtered_remaining_range.append(item)
filtered_remaining_ranges.append(filtered_remaining_range)
new_tuple = ()
# rebuild the tuple that will be the argument of next layer
for idx, rng in enumerate(filtered_remaining_ranges):
new_tuple += (rng, remaining_criteria[idx])
# only consider the minimum number across all layer responses
return min(countifs(*new_tuple), len(indexes))
else:
return float('inf')
def countifs(*args):
# Excel reference: https://support.office.com/en-us/article/
# COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
if len(args) % 2 != 0:
raise PyCelException('excellib.countifs() must have a '
'pair number of arguments, here %d' % len(args))
index_counts = Counter(it.chain.from_iterable(
find_corresponding_index(rng, criteria)
for rng, criteria in zip(args[0::2], args[1::2])))
ifs_count = len(args) // 2
return len(tuple(idx for idx, cnt in index_counts.items()
if cnt == ifs_count))
def countifs(
*args
): # Excel reference: https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
arg_list = list(args)
l = len(arg_list)
if l % 2 != 0:
raise Exception(
'excellib.countifs() must have a pair number of arguments, here %d'
% l)
if l >= 2:
indexes = find_corresponding_index(
args[0], args[1]) # find indexes that match first layer of countif
remaining_ranges = [
elem for i, elem in enumerate(arg_list[2:]) if i % 2 == 0
] # get only ranges
remaining_criteria = [
elem for i, elem in enumerate(arg_list[2:]) if i % 2 == 1
] # get only criteria
filtered_remaining_ranges = []
for range in remaining_ranges: # filter items in remaining_ranges that match valid indexes from first countif layer
filtered_remaining_range = []
for index, item in enumerate(range):
if index in indexes:
filtered_remaining_range.append(item)
filtered_remaining_ranges.append(filtered_remaining_range)
new_tuple = ()
for index, range in enumerate(
filtered_remaining_ranges
): # rebuild the tuple that will be the argument of next layer
new_tuple += (range, remaining_criteria[index])
return min(
countifs(*new_tuple), len(indexes)
) # only consider the minimum number across all layer responses
else:
return float('inf')
def sumif(rng, criteria, sum_range=None):
"""
You use the SUMIF function to sum the values in a range that meet criteria that you specify.
For example, suppose that in a column that contains numbers, you want to sum only the values
that are larger than 5. You can use the following formula: =SUMIF(B2:B25,">5")
:param rng: Required. The range of cells that you want evaluated by criteria. Cells in each
range must be numbers or names, arrays, or references that contain numbers. Blank and
text values are ignored. The selected range may contain dates in standard Excel format.
:param criteria: Required. The criteria in the form of a number, expression, a cell reference,
text, or a function that defines which cells will be added. For example, criteria can be
expressed as 32, ">32", B5, "32", "apples", or TODAY().
:param sum_range: Optional. The actual cells to add, if you want to add cells other than those
specified in the range argument. If the sum_range argument is omitted, Excel adds the cells
that are specified in the range argument (the same cells to which the criteria is applied).
:return: the total sum of the elements that meet the criteria.
.. reference::
https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b
"""
# WARNING:
# - wildcards not supported
# - doesn't really follow 2nd remark about sum_range length
sum_range = sum_range or rng
if type(rng) != list:
raise TypeError('%s must be a list' % str(rng))
if type(sum_range) != list:
raise TypeError('%s must be a list' % str(sum_range))
if isinstance(criteria, list_types) and not isinstance(criteria, (string_types, bool)):
return 0
indexes = find_corresponding_index(rng, criteria)
def f(x):
return sum_range[x] if x < len(sum_range) else 0
if len(sum_range) == 0:
return sum(map(lambda x: rng[x], indexes))
else:
return sum(map(f, indexes))
def sumifs(sum_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B
assert_list_like(sum_range)
assert len(args) and len(args) % 2 == 0, \
'Must have paired criteria and ranges'
size = len(sum_range), len(sum_range[0])
for rng in args[0::2]:
assert size == (len(rng), len(rng[0])), \
"Size mismatch criteria range, sum range"
# count the number of times a particular cell matches the criteria
index_counts = Counter(it.chain.from_iterable(
find_corresponding_index(rng, criteria)
for rng, criteria in zip(args[0::2], args[1::2])))
ifs_count = len(args) // 2
indices = tuple(idx for idx, cnt in index_counts.items()
if cnt == ifs_count)
return sum(_numerics(
(sum_range[r][c] for r, c in indices), keep_bools=True))
def sumif(range, criteria, sum_range = []): # Excel reference: https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b
# WARNING:
# - wildcards not supported
# - doesn't really follow 2nd remark about sum_range length
if type(range) != list:
raise TypeError('%s must be a list' % str(range))
if type(sum_range) != list:
raise TypeError('%s must be a list' % str(sum_range))
if isinstance(criteria, list) and not isinstance(criteria , (str, bool)): # ugly...
return 0
indexes = find_corresponding_index(range, criteria)
def f(x):
return sum_range[x] if x < len(sum_range) else 0
if len(sum_range) == 0:
return sum(map(lambda x: range[x], indexes))
else:
return sum(map(f, indexes))
def test_find_corresponding_index():
assert (0, ) == find_corresponding_index([1, 2, 3], '<2')
assert (2, ) == find_corresponding_index([1, 2, 3], '>2')
assert (0, 2) == find_corresponding_index([1, 2, 3], '<>2')
assert (0, 1) == find_corresponding_index([1, 2, 3], '<=2')
assert (1, 2) == find_corresponding_index([1, 2, 3], '>=2')
assert (1, ) == find_corresponding_index([1, 2, 3], '2')
assert (1, ) == find_corresponding_index(list('ABC'), 'B')
assert (1, 2) == find_corresponding_index(list('ABB'), 'B')
assert (1, 2) == find_corresponding_index(list('ABB'), '<>A')
assert () == find_corresponding_index(list('ABB'), 'D')
with pytest.raises(TypeError):
find_corresponding_index('ABB', '<B')
with pytest.raises(ValueError):
find_corresponding_index(list('ABB'), None)
def test_find_corresponding_index():
assert ((0, 0), ) == find_corresponding_index(((1, 2, 3), ), '<2')
assert ((0, 2),) == find_corresponding_index(((1, 2, 3), ), '>2')
assert ((0, 0), (0, 2)) == find_corresponding_index(((1, 2, 3), ), '<>2')
assert ((0, 0), (0, 1)) == find_corresponding_index(((1, 2, 3), ), '<=2')
assert ((0, 1), (0, 2)) == find_corresponding_index(((1, 2, 3), ), '>=2')
assert ((0, 1),) == find_corresponding_index(((1, 2, 3), ), '2')
assert ((0, 1),) == find_corresponding_index((list('ABC'), ), 'B')
assert ((0, 1), (0, 2)) == find_corresponding_index((list('ABB'), ), 'B')
assert ((0, 1), (0, 2)) == find_corresponding_index((list('ABB'), ), '<>A')
assert () == find_corresponding_index((list('ABB'), ), 'D')
with pytest.raises(TypeError):
find_corresponding_index('ABB', '<B')
with pytest.raises(ValueError):
find_corresponding_index((list('ABB'), ), None)
def test_find_corresponding_index():
assert ((0, 0), ) == find_corresponding_index(((1, 2, 3), ), '<2')
assert ((0, 2),) == find_corresponding_index(((1, 2, 3), ), '>2')
assert ((0, 0), (0, 2)) == find_corresponding_index(((1, 2, 3), ), '<>2')
assert ((0, 0), (0, 1)) == find_corresponding_index(((1, 2, 3), ), '<=2')
assert ((0, 1), (0, 2)) == find_corresponding_index(((1, 2, 3), ), '>=2')
assert ((0, 1),) == find_corresponding_index(((1, 2, 3), ), '2')
assert ((0, 1),) == find_corresponding_index((list('ABC'), ), 'B')
assert ((0, 1), (0, 2)) == find_corresponding_index((list('ABB'), ), 'B')
assert ((0, 1), (0, 2)) == find_corresponding_index((list('ABB'), ), '<>A')
assert () == find_corresponding_index((list('ABB'), ), 'D')
with pytest.raises(TypeError):
find_corresponding_index('ABB', '<B')
with pytest.raises(ValueError):
find_corresponding_index((list('ABB'), ), None)