Unique count of column cells based on date column and specific text in another column issues. Nested IF's within FREQUENCY

[morlince]

So far my formula (non-working) is as follows.

=SUM(IF(FREQUENCY(IF(CritDateTest<=CritOutDate AND IF(ISNUMBER(SEARCH(Vuln1,CritVulnTitleTest)),MATCH(CritIPTest,CritIPTest,0)))),ROW(CritIPTest)-ROW(CritIPBeginTest)+1),1))
or
=SUM(IF(FREQUENCY(IF(CritDateTest<=CritOutDate,IF(ISNUMBER(SEARCH(Vuln1,CritVulnTitleTest),MATCH(CritIPTest,CritIPTest,0))),ROW(CritIPTest)-ROW(CritIPBeginTest)+1),1))

Where named =

CritDate = date column range
CritOutDate = date to compare to

CritVulnTitleTest = text range that can long string of words
vuln1 = specific word to search for in the above title cell, may be at the beginning of the string or middle.

CritIP = IP address range (No Blanks so I do not have to use CritIP<>"")
CritIPBegin = First cell in range above

I already have a formula that does works for another calculation WITHOUT adding in the text search criteria. This gives me the combined total of all that fall within the date and are unique, but I cannot get the formula to run correctly once I try to add in the additional 'and' text criteria before the Match.

=SUM(IF(FREQUENCY(IF(CritODates<=CritOutDate,MATCH(CritIP,CritIP,0)),ROW(CritIP)-ROW(CritIPBegin)+1),1))

I've been banging my head on this for awhile now... the ranges are in the 10's of thousands of rows, and it Crunches the numbers a LONG TIME... we are working on a different way to aggregate this data, but it is not ready yet and I'm tired of doing all these counts manually. There may be an easier way to spit this out, so I'm not against changing the base of how I do it if it's necessary or would calculate the totals faster.

 

[James006]

Hello,

The ranges are in the 10's of thousands of rows

Forget array formulas ... and Insert a Pivot Table ...

HTH

 

[Marcelo Branco]

So far my formula (non-working) is as follows.

=SUM(IF(FREQUENCY(IF(CritDateTest<=CritOutDate,IF(ISNUMBER(SEARCH(Vuln1,CritVulnTitleTest),MATCH(CritIPTest,CritIPTest,0))),ROW(CritIPTest)-ROW(CritIPBeginTest)+1),1))

1, The named ranges should have the same size

2. Missing a parenthesis...

Try
=SUM(IF(FREQUENCY(IF(CritDateTest<=CritOutDate,IF(ISNUMBER(SEARCH(Vuln1,CritVulnTitleTest)),MATCH(CritIPTest,CritIPTest,0))),ROW(CritIPTest)-ROW(CritIPBeginTest)+1),1))

M.

 

[morlince]

Hello,



Forget array formulas ... and Insert a Pivot Table ...

HTH

I will definitely try to build a fancy pivot table, but I dont feel like it will do all the sorting I need without a lot of customization anyways. But thanks for the suggestion and I will do more research into it. I have used them before just not with something so immense.

 

[morlince]

1, The named ranges should have the same size

2. Missing a parenthesis...

Try
=SUM(IF(FREQUENCY(IF(CritDateTest<=CritOutDate,IF(ISNUMBER(SEARCH(Vuln1,CritVulnTitleTest)),MATCH(CritIPTest,CritIPTest,0))),ROW(CritIPTest)-ROW(CritIPBeginTest)+1),1))

M.

That seems to work, thanks. Yeah, my named ranges all have the same size. I could have just used the table names for the columns just didnt at the time. Cant believe it was so easy, I probably counted those parenthesis multiple times.

 

[Marcelo Branco]

That seems to work, thanks. Yeah, my named ranges all have the same size. I could have just used the table names for the columns just didnt at the time. Cant believe it was so easy, I probably counted those parenthesis multiple times.

Glad it worked for you.

M.

 

[Aladin Akyurek]

@ morlince

You can replace this bit

ROW(CritIPBeginTest)+1

with

ROW(INDEX(CritIP,1,1))+1

and delete the name CritIPBeginTest from the Name Manager.

 

[morlince]

@ morlince

You can replace this bit

ROW(CritIPBeginTest)+1

with

ROW(INDEX(CritIP,1,1))+1

and delete the name CritIPBeginTest from the Name Manager.

Does having additional 'Names' defined cause additional overhead, or would this just be to clean up the list a bit?

 

[Aladin Akyurek]

Does having additional 'Names' defined cause additional overhead, or would this just be to clean up the list a bit?

Yes, I think both for the user and the system.

Once we have CritP, ROW(CritP)-ROW(INDEX(CritP,1,1))+1 would be also more robust to work with.