Forum Discussion
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:
| A | B | C | D |
| Plain Bagel | 7 | ||
| Plain Bagel | Bagel Spreads | Cream Cheese | 5 |
| Plain Bagel | Bagel Spreads | Butter | 2 |
| Plain Bagel | Toasted/Warmed | Yes Toasted/Warmed | 7 |
| Double Espresso | Food Selection | Plain Bagel w/ Cream Cheese | 4 |
| Hot Chai Latte | Food Selection | Plain Bagel w/ No Spread | 2 |
| Iced Coffee | Food Selection | Plain Bagel w/ Butter | 6 |
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.
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
12 Replies
Aking123, if column D is coming in as text, then run the following code,
Dim Endrw As Integer Sub ConvertRangeToNumber() ' With ThisWorkbook.Sheets("Sheet1") .Select Endrw = ActiveSheet.UsedRange.Rows.Count End With ' Set Rng = ThisWorkbook.Sheets("Sheet1").Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row) For Each cel In Rng.Cells cel.Value = CSng(cel.Value) Next cel ' Cells(1, 5).Formula = "=SUMIFS(D1:D" & Endrw & ",A1:A" & Endrw & ",""Plain Bagel"",B1:B" & Endrw & ","""",C1:C" & Endrw & ","""")+SUMIFS(D1:D" & Endrw & ",C1:C" & Endrw & " ,""*Plain Bagel*"")" ' End SubThe first section will determine the number of rows being used in the data, then it will check each cell in column D and change it from text to a number, then the last line will place the formula in column E to get the total you are looking for.
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*")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
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.
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."
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)
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?
