Forum Discussion

AKing123's avatar
AKing123
Copper Contributor
Jun 12, 2026
Solved

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
  • IlirU's avatar
    IlirU
    Jun 15, 2026

    AKing123​,

    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

MKoski's avatar
MKoski
Brass Contributor
Jun 17, 2026

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 Sub

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