I have a spreadsheet that has several columns. I'm only going to show data from 2 of them here, because they're the 2 that I'm dealing with in this problem.

The first column is IP Addresses. The second column is how long ago the last response was or the last response date:

What I need to do is to get a few counts worked out. I want to know how many from each IP subnet have

1. A response older than 1 week
2. A Response older then 1 month.

In the sample data you can see 3 IP subnets: 10.4, 10.16, and 10.64. I am expecting to get results like:

I have a formula for the "> Week", but I don't like it. I am not able to figure out how to count based on the number at the beginning of the text in that column. I tried a formula like this:

=COUNTIFS(AllIPAddresses,"10.1.*",AllResponses, NUMBERVALUE(LEFT(AllResponses, FIND(" ",AllResponses)))&">7")

Obviously this doesn't work. It gives me a column full of 0's.

What I have working for the "> Week" column:

=COUNTIFS(AllIPAddresses,CONCAT(A2,"*"),AllResponses,"<>7 days",AllResponses,"<>6 days",AllResponses,"<>5 days",AllResponses,"<>4 days",AllResponses,"<>3 days",AllResponses,"<>2 days",AllResponses,"<>Today",AllResponses,"<>Yesterday")

But like I said, I don't like it as it is just looking at the column and not counting 8 of the options. I would prefer if I could have a way to get it to look at the column and count those whose number of days is > 7. Something simple would be great, but something that is shorter and/or simpler than what I have I'll take. And I cannot reuse that effectively for the "> month" result because then I'd have to list some 30 different options that I don't want to count.
It would be better to have it look for the 1 option that I do want.

I'm hoping for something like:

• First COUNTIFS counts all the text that have a number > 7
• Second COUNTIFS counts all the dates that are more than 7 days before today

=COUNTIFS(AllIPAddresses, CONCAT(A2,"*"),AllResponses, LEFT(AllResponse,2)&">7") COUNTIFS(AllIPAddresses, CONCAT(A2,"*"),AllResponses,"<"&today()-7)

And then I can reuse this for the "> month" by changing the 7 to a 30.

Though I know this formula doesn't work.

Any assistance with this problem would be appreciated!

Some Notes about my formulas
For ease of use I have named ranges:
AllIPAddresses = A2:A700
AllResponses = B2:B700
(in my formula for > week) A2 is referring to the "10.1." so that the CONCAT will give the result of "10.1.*" to the COUNTIFS

CodePudding user response：

This could be accomplished using:

=LET(data,A2:B15,
     _d1,INDEX(data,,1),
     _d2,INDEX(data,,2),
         lr,TODAY()-IF(ISNUMBER(_d2),_d2,TODAY()-(LEFT(_d2,LEN(_d2)-LEN(" days")))),
         lft,TEXTBEFORE(_d1,".",2),
         unq,UNIQUE(lft),
         sq,SEQUENCE(COUNTA(lft),,1,0),
         mm,--(TRANSPOSE(unq)=lft),
            wk,MMULT(--(TRANSPOSE(lft)=unq)*TRANSPOSE(lr>7),sq),
            mn,MMULT(--(TRANSPOSE(lft)=unq)*TRANSPOSE(lr>30),sq),
               stack,HSTACK(unq,wk,mn),
VSTACK({"IP Subnet","> Week","> Month"},stack))

lft uses TEXTBEFORE to get the first 2 sections of the IP address. lr calculates the number of days of the last response compared to today. unq is the unique values of lft (IP subnet). wk uses MMULT to calculate the conditional count of unique IP subnet values where lr is greater than 7. mn is the same as wk, but where lr is greater than 30.

CodePudding user response：

Using new 365 functions, generate unique IPs value, and count for >7 and >30 days. If date retrieve differences, otherwise extract days number:

=LET(IP,TEXTBEFORE(AllIPAddresses,".",2),U,UNIQUE(IP),S,SEQUENCE(COUNTA(AllIPAddresses),,1,0),D,IFERROR(TODAY()-AllResponses,TEXTBEFORE(AllResponses," ")*1),W,MMULT((TRANSPOSE(IP)=U)*(TRANSPOSE(D)>7),S),M,MMULT((TRANSPOSE(IP)=U)*(TRANSPOSE(D)>30),S),HSTACK(U,W,M))

with HSTACK combine columns results.