Trouble with getting CountIFS to work in FormulaArray

[utahwingman]

Thank you for all those have been contribution to the forum. I have been using it for a while and finding many solutions for my problems. This latest one I couldn't find a solution any where.

I have this example data set:

B	G
Notification <tbody> </tbody>	Severity Level Actua <tbody> </tbody>
411541993 <tbody> </tbody>	Actual Severity Level 1 <tbody> </tbody>
411542368 <tbody> </tbody>	Actual Severity Level 2 <tbody> </tbody>
411542368 <tbody> </tbody>	Actual Severity Level 3 <tbody> </tbody>
411542368 <tbody> </tbody>	Actual Severity Level 0 <tbody> </tbody>
411543263 <tbody> </tbody>	Actual Severity Level 0 <tbody> </tbody>
411543905 <tbody> </tbody>	Actual Severity Level 1 <tbody> </tbody>
411544013 <tbody> </tbody>	Actual Severity Level 1 <tbody> </tbody>
411544173 <tbody> </tbody>	Actual Severity Level 0 <tbody> </tbody>
411544287 <tbody> </tbody>	Actual Severity Level 3 <tbody> </tbody>

<tbody>
</tbody>

I'm trying to find the number of unique notifications and number of notifications with level 1-5. I found a solution to do in the forum by entering the following equations
{=SUM(1/COUNTIF(Sheet1!B2:B10,Sheet1!B2:B10))} got the result of 7 it works!!
{=SUM(IF("Actual Severity Level 0"=Sheet1!G2:G20,1/(COUNTIFS(Sheet1!G2:G20,"Actual Severity Level 0",Sheet1!B2:B20,Sheet1!B2:B20)),0))} got the result of 3
Because I want to only count the highest level in the notification, so in notification 41154368, I only want to count the Level 3 and ignore the Level 2 and Level 0 ones. Can this be done?

Because I'm trying to make it to work for any data set I'll get, I tried to do it in VBA and use dynamic range. Suppose I'll have the data set copy to Sheet1. I have these define:

Set sht = ThisWorkbook.Sheets("Sheet1")
LastRow = sht.Cells(Rows.Count, 1).End(xlUp).Row
'find column for notification, actual and potential severity levels
NotiCol = Application.WorksheetFunction.Match("Notification", sht.Range("1:1"), 0)
ActualCol = Application.WorksheetFunction.Match("Severity Level Actua", sht.Range("1:1"), 0)
PotentCol = Application.WorksheetFunction.Match("Severity Level Poten", sht.Range("1:1"), 0)


'set the ranges
Set NotiRange = Range(sht.Cells(2, NotiCol), sht.Cells(LastRow, NotiCol))
Set ActualRange = Range(sht.Cells(2, ActualCol), sht.Cells(LastRow, ActualCol))
Set PotentRange = Range(sht.Cells(2, PotentCol), sht.Cells(LastRow, PotentCol))


'count number of rows and number of unique notifications
Range("c2") = LastRow - 1
Range("c3").FormulaArray = "Sum(1/COUNTIF(NotiRange, NotiRange))"

'count number of actual severity levels
Range("c6").FormulaArray = "=sum(if(""Actual Severity Level 1""=AcutalRange,1/(COUNTIFS(ActualRange,""Actual Severity Level 1"",NotiRange,NotiRange)),0))"

I checked the ranges and they return the correct arrays with msgbox. However I cannot get the FormulaArray to work. It seems like it doesn't understand the ranges and just put the same text in the cell and get a "#NAME?".

Thanks in advance

 

[Marcelo Branco]

Welcome to Mr Excel

Some thoughts
1. do not understand why you used column 1 to get the last row with data
LastRow = sht.Cells(Rows.Count, 1).End(xlUp).Row
In the code below i used NotiCol

2.you must build the formula strings carefully using the range addresses

3. Use Option Explicit which requires all variables are declared - it's a good programming practice and avoids many errors

Sub aTest()
    Dim sht As Worksheet, LastRow As Long, ActualCol As Long, NotiCol As Long, PotentCol As Long
    Dim NotiRange As Range, ActualRange As Range, PotentRange As Range
    Dim strFormula1 As String, strFormula2 As String
    
    Set sht = ThisWorkbook.Sheets("Sheet1")
    
    'find column for notification, actual and potential severity levels
    NotiCol = Application.WorksheetFunction.Match("Notification", sht.Range("1:1"), 0)
    ActualCol = Application.WorksheetFunction.Match("Severity Level Actua", sht.Range("1:1"), 0)
    PotentCol = Application.WorksheetFunction.Match("Severity Level Poten", sht.Range("1:1"), 0)
       
    'find LastRow
    LastRow = sht.Cells(Rows.Count, NotiCol).End(xlUp).Row
    
    'Just for checking purposes...
    Debug.Print NotiCol
    Debug.Print ActualCol
    Debug.Print PotentCol
    Debug.Print LastRow
    
    'set the ranges
    Set NotiRange = Range(sht.Cells(2, NotiCol), sht.Cells(LastRow, NotiCol))
    Set ActualRange = Range(sht.Cells(2, ActualCol), sht.Cells(LastRow, ActualCol))
    Set PotentRange = Range(sht.Cells(2, PotentCol), sht.Cells(LastRow, PotentCol))
        
    'build the string of the first formula
    strFormula1 = Replace("=Sum(1/COUNTIF(@,@))", "@", NotiRange.Address)
    Range("c3").FormulaArray = strFormula1
    
    'build the string of the second formula
    strFormula2 = Replace("=sum(if(#=""Actual Severity Level 1"",1/COUNTIFS(#,""Actual Severity Level 1"",@,@)))", "#", ActualRange.Address)
    strFormula2 = Replace(strFormula2, "@", NotiRange.Address)
    Range("c6").FormulaArray = strFormula2
End Sub

Hope this helps

M.

 

[utahwingman]

Thank you Marcelo,

It is definitely a good trick using the formula string. However now it is not looking at "Sheet1". I thought NotiRange is tie to the range on Sheet1. But it is not. It is looking at the active sheet where I'm at, so it's giving me the wrong answer.

Any suggestions on how to fix the second problem that I have:
Because I want to only count the highest level in the notification, so in notification 41154368, I only want to count the Level 3 and ignore the Level 2 and Level 0 ones. Can this be done? So I should only have 2 Level 0, 3 Level 1, 0 Level 2, and 2 Level 3 which give me a total of 7 unique count.

Thanks again for replying so quickly

 

[Marcelo Branco]

It is definitely a good trick using the formula string. However now it is not looking at "Sheet1". I thought NotiRange is tie to the range on Sheet1. But it is not. It is looking at the active sheet where I'm at, so it's giving me the wrong answer.

Sorry, i overlooked this...

Just below this code line
Set sht = ThisWorkbook.Sheets("Sheet1")

insert
sht.Activate

M.

 

[Marcelo Branco]

Any suggestions on how to fix the second problem that I have:
Because I want to only count the highest level in the notification, so in notification 41154368, I only want to count the Level 3 and ignore the Level 2 and Level 0 ones. Can this be done? So I should only have 2 Level 0, 3 Level 1, 0 Level 2, and 2 Level 3 which give me a total of 7 unique count.

About the second problem...sorry, i'm confused.
I'm not understanding what you are trying to do...

Could you provide some examples - data sample and expected result(s)?

M.

 

[utahwingman]

Sure thing. Maybe there is a better way to do this than where I'm heading. I have this example set of data on Sheet1 and I would like the results display in Sheet2 because Sheet1 will be full of data already. In this example the range are in column B and G. But it can be in any columns and size of the data varies depends on the user, that's why I had the dynamic range set up.

Column B Column G
Notification Severity Level Actua
411541993 Actual Severity Level 1
411542368 Actual Severity Level 2
411542368 Actual Severity Level 3
411542368 Actual Severity Level 0
411543263 Actual Severity Level 0
411543905 Actual Severity Level 1
411544013 Actual Severity Level 1
411544173 Actual Severity Level 0
411544287 Actual Severity Level 3

So what what I'm looking for is 7 unique notifications because 411542368 repeated 3 times. and the count of levels should be:
Level 0 - 2
Level 1 - 3
Level 2 - 0
Level 3 - 2

which equals to 7, same as unique number of notifications. one of the Level 0 and Level 1 do not count because they are from the same notification 411542368. I only want to count the highest level. Hope this better explain what I'm trying to do.

 

[Marcelo Branco]

We should use a helper column to make things easier

Something like

	B​	C​	D​	E​	F​	G​	H​	I​
1​	Notification​			Count​		Severity Level Actua​		MAX​
2​	411541993​		Level 0​	2​		Actual Severity Level 1​		Level 1​
3​	411542368​		Level 1​	3​		Actual Severity Level 2​		Level 3​
4​	411542368​		Level 2​	0​		Actual Severity Level 3​		Level 3​
5​	411542368​		Level 3​	2​		Actual Severity Level 0​		Level 3​
6​	411543263​					Actual Severity Level 0​		Level 0​
7​	411543905​					Actual Severity Level 1​		Level 1​
8​	411544013​					Actual Severity Level 1​		Level 1​
9​	411544173​					Actual Severity Level 0​		Level 0​
10​	411544287​					Actual Severity Level 3​		Level 3​

Helper column
Array formula in I2 copied down
="Level "&MAX(IF($B$2:$B$10=B2,RIGHT($G$2:$G$10)+0))
Ctrl+Shift+Enter

Array formula in E2 copied down (assumes the values in column B are numbers, not text)
=SUM(IF(FREQUENCY(IF($I$2:$I$10=D2,$B$2:$B$10),$B$2:$B$10),1))
Ctrl+Shift+Enter

M.

 

[utahwingman]

I see. I was hoping it can be done as a macro because the data set is quite large and it can be difficult for the users who are not familiar with spreadsheet to manipulate their data. Depends on how they run the report, it can be 10-50 different columns with data. I was trying to minimize the work require them to make any changes to their data sheet. I tried to teach them how to use PivotTable and ended up doing it myself.

Thank you for your help and suggestions.