Forum Discussion

AKing123's avatar
AKing123
Copper Contributor
Jun 12, 2026
Solved

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
    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

MKoski's avatar
MKoski
Brass Contributor
Jun 15, 2026

I copied your sample/pasted into a workbook and the following code worked with (1) caveat. I needed to retype manually the quantities into column D or it returned 0 for a total. It appears column D is not being brought in as numbers or integers. Note I tried just formatting the cells as numbers in column D, but that did not work.

=SUMIFS(D1:D7,A1:A7,"Plain Bagel",B1:B7,"",C1:C7,"")+SUMIFS(D1:D7,C1:C7,"*Plain Bagel*")