Forum Discussion
CountIFS/SumIFS Question
Hi AKing123ā,
You can use this formula (see the screenshot):
=LET(num, D2:D8, pb, "*Plain Bagel*", SUM(TOCOL(SEARCH(pb, A2:A8) * BYROW(B2:C8 = "", AND) * num, 3), SUMIFS(num, C2:C8, pb)))Change the range in formula as per you need.
Or you can use below formula:
=LET(
data, A2:D8,
txt, DROP(data,, -1),
num, TAKE(data,, -1),
srch, SEARCH("Plain Bagel", txt),
SUM(TOCOL(srch * BYROW(txt = "", OR) * num, 3),
TOCOL(CHOOSECOLS(srch, 3) * num, 3))
)Change the range in formula as per you need.
Note: The second formula is easier to maintain because you only need to adjust one range if it becomes necessary, whereas in the first formula you have to update several ranges at the same time.
HTH
IlirU
Thank you for the assist! When I input the first formula into the actual sheet, it didn't count the first number with empty cells next to it, though I clearly see it working here. When I attempted to enter it into the sheet, I was given a #Calc error saying empty arrays are not supported.
As for the second formula, I'll admit I'm not sure how it's meant to work, so I might have adjusted it incorrectly. However, it too is giving me the same #Calc error for the same reasoning.