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
if len(args) % 2 != 0:
raise PyCelException('excellib.countifs() must have a '
'pair number of arguments, here %d' % len(args))
if len(args):
# 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(args[2:]) if i % 2 == 0
]
# get only criteria
remaining_criteria = [
elem for i, elem in enumerate(args[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 index, item in enumerate(rng):
if index 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 index, rng in enumerate(filtered_remaining_ranges):
new_tuple += (rng, remaining_criteria[index])
# only consider the minimum number across all layer responses
return min(countifs(*new_tuple), len(indexes))
else:
return float('inf')
with pytest.raises(AssertionError):
a('1:2').resolve_range
@pytest.mark.parametrize(
'ref, expected', (
# valid addresses
('a_table[[#This Row], [col5]]', 'E5'),
('a_table[[#All],[col3]]', 'C1:C8'),
('a_table[[#All],[col3]:[col4]]', 'C1:D8'),
('a_table[[#Headers],[col4]]', 'D1'),
('a_table[[#Headers],[col2]:[col5]]', 'B1:E1'),
# Not Supported
('a_table[[#Headers],[#Data],[col4]]', PyCelException('D1:D7')),
('a_table[[#Data],[col4]:[col4]]', 'D2:D7'),
('a_table[[#Data],[col4]:[col5]]', 'D2:E7'),
('a_table[[#Totals],[col2]]', 'B8'),
('a_table[[#Totals],[col3]:[col5]]', 'C8:E8'),
('a_table[[#This Row], [col5]]', 'E5'),
('a_table[[col4]:[col4]]', 'D2:D7'),
('a_table[@col5]', 'E5'),
('a_table[@[col2]]', 'B5'),
('a_table[#This Row]', 'A5:E5'),
('a_table[@]', 'A5:E5'),
('a_table[]', 'A2:E7'),
# bad table / cell
('JUNK[]', PyCelException()),