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

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

    Since the data is imported those numbers are probably TEXT and not numbers.  Try this variation instead:

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

    alternatively you can use 'Text to Columns ' (on the Data tab) to convert the text in column D from text to numbers.

    (Note: as I just saw MKoski​ reply, changing the cell FORMAT from text or general to number will NOT actually convert the values in those cells from text to numbers, it only changes the formatting of those cells and since Excel thinks those are text it will still treat them as text.  Just like if the value is "Joe" changing the cell format from text or general to number will not magically convert "Joe" into some numberic value.  As I noted above, you can however use the 'Text to Columns' tool to convert them.  Also, using them in a calculation will also have Excel try to convert the text into a number and that is what I did in the SUMPRODUCT variation above, where the column of D1:D999 is multiplied by the output of the conditionals.  Note that is important not only for your column D that is text but all of those conditional return a True/False which excel then needs to convert to 1/0 for that formula to work)