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 delivered to me, as it's a downloaded file from a 3rd party.

Here's a sample of what I'm looking at:

ABCD
Plain Bagel             7
Plain BagelBagel SpreadsCream Cheese           5
Plain BagelBagel SpreadsButter           2
Plain BagelToasted/WarmedYes Toasted/Warmed           7
Double EspressoFood SelectionPlain Bagel w/ Cream Cheese4
Hot Chai LatteFood SelectionPlain Bagel w/ No Spread2
Iced CoffeeFood SelectionPlain Bagel w/ Butter6

 

Basically, I'm trying to add the numbers in column "D," but only if these requirements are met: If column A says Plain Bagel, column's B and C have to be empty. If column C has any type of Plain Bagel, then those should also be added.

In case it's needed: I'm currently using Excel for the web, but I do have access to the desktop version as well. 

excel
Formulas and Functions

6 Replies

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

    • 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."

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jun 12, 2026

    maybe try:

    =SUMIFS(D1:D999, A1:A999, "Plain Bagel", B1:B999, "", C1:C999, "") + SUMIFS(D1:D999, C1:C999,"*Plain Bagel*")

    • AKing123's avatar
      AKing123
      Copper Contributor
      Jun 13, 2026

      Thank you for the assist! Unfortunately, when I put it into the actual sheet it still brings it up as "0." Is it possible there's just to much information for excel to search through?

    • AKing123's avatar
      AKing123
      Copper Contributor
      Jun 13, 2026

      Thank you for the assist! Oddly enough, when used on the example sheet like the one I posted here, it worked fine. But when I tried to use it on the actual sheet with all of the data and corrected columns, it kept coming back as "0." Would hidden or differently named columns make a difference?