Forum Discussion

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

AKing123's avatar
AKing123
Copper Contributor
Jun 13, 2026

Thank you for the assist! When I plugged in the first formula with the adjusted ranges, it only added the numbers for when columns B and C were empty, but ignored the other conditions.

 

As for the second formula, I'll be honest and say I don't know how to properly use it. I was able to put it into the sheet, but the error message I'm getting says "Empty Arrays are Not Supported."

  • IlirU's avatar
    IlirU
    Iron Contributor
    Jun 15, 2026

    AKing123​,

    Perhaps some of the numbers you have in column D (see screenshot I gave you in the previous post) are set as text. If so, then you can use the following formula.

    =LET(
     data, A2:D8,
      txt, DROP(data,, -1),
      num, --REGEXEXTRACT(TAKE(data,, -1), "\d+", 1),
     srch, SEARCH("Plain Bagel", txt),
           SUM(TOCOL(srch * BYROW(txt = "", OR) * num, 3),
               TOCOL(CHOOSECOLS(srch, 3) * num, 3))
)

    If you still have any possible problems, then I recommend that you post your file here in this forum (without sensitive data, email addresses, etc.). This way your problem can be seen / understood better and the contributors of this forum will give you the appropriate help.

    HTH

    IlirU