Forum Discussion
CountIFS/SumIFS Question
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
maybe try:
=SUMIFS(D1:D999, A1:A999, "Plain Bagel", B1:B999, "", C1:C999, "") + SUMIFS(D1:D999, C1:C999,"*Plain Bagel*")
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?
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)
Again, thank you so much for your help!
As it turns out, it was that the numbers were being read as text. Your formula is working great, thank you so much!
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?
