utahwingman
New Member
- Joined
- Jul 8, 2015
- Messages
- 4
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:
<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
I have this example data set:
B | G | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
<tbody> </tbody> |
<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