Forum Discussion

Copper Contributor

Jun 12, 2026

CountIFS/SumIFS Question

Hello everyone, I'm working with a lot of data, and trying to find a way to add quantities already in the sheets but spread out over 500 or more rows. I'm not able to change how the data is delivere...

excel

Formulas and Functions

IlirU

Iron Contributor

Jun 13, 2026

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

AKing123

Copper Contributor

Jun 13, 2026

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.