Forum Discussion

AKing123's avatar
AKing123
Occasional Reader
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's avatar
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