Trouble with getting CountIFS to work in FormulaArray

U

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]Notification[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Severity Level Actua[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411541993[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411542368[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411542368[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411542368[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411543263[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411543905[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411544013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411544173[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]411544287[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]Actual Severity Level 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
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
Code: 
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.



 
Upvote 0
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
 
Upvote 0
utahwingman said:
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.
Click to expand...

Sorry, i overlooked this...

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

insert
sht.Activate

M.
 
Upvote 0
utahwingman said:
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.
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
We should use a helper column to make things easier

Something like


[Table="class: grid"][tr][td] [/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][/tr]
[tr][td]
1​
[/td][td]
Notification​
[/td][td] [/td][td] [/td][td]
Count​
[/td][td] [/td][td]
Severity Level Actua​
[/td][td] [/td][td]
MAX​
[/td][/tr]


[tr][td]
2​
[/td][td]
411541993​
[/td][td] [/td][td]
Level 0​
[/td][td]
2​
[/td][td] [/td][td]
Actual Severity Level 1​
[/td][td] [/td][td]
Level 1​
[/td][/tr]


[tr][td]
3​
[/td][td]
411542368​
[/td][td] [/td][td]
Level 1​
[/td][td]
3​
[/td][td] [/td][td]
Actual Severity Level 2​
[/td][td] [/td][td]
Level 3​
[/td][/tr]


[tr][td]
4​
[/td][td]
411542368​
[/td][td] [/td][td]
Level 2​
[/td][td]
0​
[/td][td] [/td][td]
Actual Severity Level 3​
[/td][td] [/td][td]
Level 3​
[/td][/tr]


[tr][td]
5​
[/td][td]
411542368​
[/td][td] [/td][td]
Level 3​
[/td][td]
2​
[/td][td] [/td][td]
Actual Severity Level 0​
[/td][td] [/td][td]
Level 3​
[/td][/tr]


[tr][td]
6​
[/td][td]
411543263​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Actual Severity Level 0​
[/td][td] [/td][td]
Level 0​
[/td][/tr]


[tr][td]
7​
[/td][td]
411543905​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Actual Severity Level 1​
[/td][td] [/td][td]
Level 1​
[/td][/tr]


[tr][td]
8​
[/td][td]
411544013​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Actual Severity Level 1​
[/td][td] [/td][td]
Level 1​
[/td][/tr]


[tr][td]
9​
[/td][td]
411544173​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Actual Severity Level 0​
[/td][td] [/td][td]
Level 0​
[/td][/tr]


[tr][td]
10​
[/td][td]
411544287​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Actual Severity Level 3​
[/td][td] [/td][td]
Level 3​
[/td][/tr]
[/table]

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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
F
  • Solved
Just cant get range to work!
Replies
3
Views
319
Joe4
Joe4
M
  • Question Question
WorksheetFunction.CountIf with a range of values as criteria
Replies
5
Views
551
Alex Blakenburg
A
N
  • Solved
Loop to get emails from Outlook 365 has broken, and I cant work out why !
Replies
3
Views
769
netrixuser
N
J
  • Question Question
macro to Save to Folder not working
Replies
0
Views
398
jmurray394
J

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top