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

2 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

  • 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*")