Excel counting with multiple criteria and multiple instances in a single cell

Question

I need help with a function which will find all values of 1 in column A (e.g. A1). For each value of 1, I need the function to count the number of IP addresses beginning with either 178. or 14. in the corresponding column B (e.g. B1). The IP addresses are separated by a comma & space, e.g. 178.5.4.3, 178.5.4.4

I was able to count the total instances of 178. in column B using this formula:

=SUM(LEN(A2:A11)-LEN(SUBSTITUTE(A2:A11,"178.",""))/LEN("178."))

Or this one by adding four spaces in the substitute function:

=SUM(LEN(A2:A11)-LEN(SUBSTITUTE(A2:A11,"178."," ")))

But, I cannot seem to find a way to search for a 1 in column A (without counting it) and then count the number of 178. and 14. in the corresponding B column.

The purpose is to find out how many IP addresses are in use in zone 1, 2, 3, etc.

1   14.38.227.88
1   14.38.227.103
1   14.38.227.104
2   178.32.38.106, 178.32.34.241, 178.32.33.65, 178.32.32.20, 178.32.34.135
2   178.32.38.187, 178.32.38.52, 178.32.38.50, 178.32.38.4, 178.32.36.223, 178.32.35.197, 178.32.34.254, 178.32.32.160, 178.32.32.159, 178.32.32.76, 178.32.32.39, 178.32.32.38, 178.32.32.26
3   178.32.33.61, 178.32.33.57
3   178.32.38.180, 178.32.38.119, 178.32.38.64, 178.32.38.62, 178.32.37.215, 178.32.35.108, 178.32.35.42, 178.32.34.242, 178.32.34.237, 178.32.34.1, 178.32.33.60, 178.32.33.59, 178.32.32.78, 178.32.33.56
4   178.32.39.6, 178.32.38.186, 178.32.38.115, 178.32.38.58, 178.32.37.216, 178.32.34.236, 178.32.33.206, 178.32.33.66, 178.32.33.55, 178.32.33.54, 178.32.32.77, 178.32.32.40, 178.32.32.33, 178.32.38.166
4   178.32.33.53, 178.32.38.167
4   178.32.33.68

Answer 1

Assuming no Excel Constraints as per the tags posted, then the following formulas should work to accomplish the desired output.

---

• Formula used in cell A12

=LET(
     α, A1:A10,
     φ, B1:B10,
     UNIQUE(HSTACK(α,MMULT(N(α=TOROW(α)),LEN(φ)-LEN(SUBSTITUTE(φ,",",))+1))))

---

Edit:

As per comments of OP

Thank you. This is a good start and looks like it is going to work. Only need one change. The first octet in the IP address is important. There may be other IP addresses listed, but I only want to count the ones that start with 178. or 14. Can you adjust this function to do that?

---

---

• Formula used in cell A12

=LET(
     α, TEXTBEFORE(TEXTAFTER(", "&B1:B10,", ",SEQUENCE(,99)),"."),
     φ, TOCOL(IFS(ISNUMBER(XMATCH(α,{"178","14"})),A1:A10),3),
     UNIQUE(HSTACK(φ,MMULT(N(φ=TOROW(φ)),φ^0))))

---

• α --> First split the IP addresses data into columns,
• φ --> Return an array of Zones stacked into a single column for those have IP addresses starting with 178. & 14. by excluding the rest which don't start with the same.
• Lastly, do a matrix product using MMULT( ) of two arrays to get the counts, and by wrapping within a UNIQUE() after using HSTACK( ) to combine both the arrays, excludes the duplicates.

---

Also, the above formula assumes per row there will be a max of 99 IP Addresses, if its not, then replace the 99 with this to make it dynamic -- MAX(LEN(B1:B10)-LEN(SUBSTITUTE(B1:B10,",",))+1) , that said the formula will be,

=LET(
     Ψ, MAX(LEN(B1:B10)-LEN(SUBSTITUTE(B1:B10,",",))+1),
     α, TEXTBEFORE(TEXTAFTER(", "&B1:B10,", ",SEQUENCE(,Ψ)),"."),
     φ, TOCOL(IFS(ISNUMBER(XMATCH(α,{"178","14"})),A1:A10),3),
     UNIQUE(HSTACK(φ,MMULT(N(φ=TOROW(φ)),φ^0))))

---

---

Updated Formula:

---

The formula is updated as per OP's comments:

---

OP Says:

Thank you. I see it working with data in the same spreadsheet. If I try to reference cells in another spreadsheet it does not appear to work. Where 'All_Devices_Requiring_IP_Space' is the other spreadhseet in the same workbook and IP addresses are in column E and zone numbers are in column D.

I see it working in your link. However, every time I cut and paste the formula into a new cell i get the #CALC! error in my spreadsheet.

I found the problem. The function does not take blank spaces in the zone column. The data must exist in every row of both columns. This won't work: ` 1 178.32.33.68 5 14.38.227.88 5 14.38.227.103 14.38.227.104 178.32.33.68 14.38.227.88 5 14.38.227.103 6 14.38.227.104

---

• Formula used in cell C1

=LET(
     δ, FILTER(All_Devices_Requiring_IP_Space!D1:E27,All_Devices_Requiring_IP_Space!D1:D27<>""),
     Ψ, MAX(LEN(INDEX(δ,,2))-LEN(SUBSTITUTE(INDEX(δ,,2),",",))+1),
     α, TEXTBEFORE(TEXTAFTER(", "&INDEX(δ,,2),", ",SEQUENCE(,Ψ)),"."),
     Φ, TOCOL(IFS(ISNUMBER(XMATCH(α,{"178","14"})),INDEX(δ,,1)),3),
     UNIQUE(HSTACK(Φ,MMULT(N(Φ=TOROW(Φ)),Φ^0))))

---

Answer 2

Count Substrings

=LET(data,A2:B21,ucol,1,vcol,2,crit,{"A.";"B."},dlm,", ",
    ud,CHOOSECOLS(data,ucol),
    vd,CHOOSECOLS(data,vcol),
    u,UNIQUE(ud),
    v,BYROW(u,LAMBDA(r,LET(
        f,FILTER(vd,ud=r),
        sc,TEXTSPLIT(TEXTJOIN(dlm,,f),dlm),
        vc,COUNT(SEARCH(TOCOL(crit&"*"),sc)),
    vc))),
HSTACK(u,v))

=LET(data,A2:B21,ucol,1,vcol,2,crit,{"A.";"B."},dlm,", ",
    ud,CHOOSECOLS(data,ucol),
    vd,CHOOSECOLS(data,vcol),
    u,UNIQUE(ud),
HSTACK(u,BYROW(u,LAMBDA(r,
    COUNT(SEARCH(TOCOL(crit&"*"),
        TEXTSPLIT(TEXTJOIN(dlm,,
            FILTER(vd,ud=r)),dlm)))))))