Countif function

[SanFranz]

Hi All,
Could you please suggest how can I amend this formula whereby I can replace the part of countif(I10:I11:I13:I14:I16:I17:I18:I19:I20:I21:I28 as I would consider those as single cells rather than a series? In fact the cells in between the selection e.g. I12,I15 still applies the countif function and they shouldn't. In fact, if I replace the : with , the formula has too many conditions and I get stuck

=IF(SUM(L9:L21)*100+IF(SUM(COUNTIF(I10:I11:I13:I14:I16:I17:I18:I19:I20:I21:I28,'FATF Feb23'!B5:B17)),48,0)>48,48,((SUM(L9:L21)*100)))

 

[RudRud]

1. restricted countries if exist in column e backgroundcolor yellow then G21 = 100
2. restricted countries if exist in column e backgroundcolor blue then G21 total all column F

Book2
	A	B	C	D	E	F	G
7	Restricted Countries
8					List Country
9	Spain			Registered	Germain	2%
10	Germain			Managed	Spain	5%
11	France			Operating	Australi	4%
12				UBO Nationality	Gibraltar	6%
13				UBO Residencey	USA	2%
14				Jurisdiction		7%
15				Licensed	Andorra	2%
16				Top 3 countries	Cile	3%
17					UK	7%
18					Malta	1%
19				Top 3 countries to pay	Canada	2%
20					Russia	8%
21					Serbia	1%
Sheet1

How about this condition? it will total column F because the first restricted countries is in background blue?

 

[SanFranz]

1. restricted countries if exist in column e backgroundcolor yellow then G21 = 100
2. restricted countries if exist in column e backgroundcolor blue then G21 total all column F

Book2
	A	B	C	D	E	F	G
7	Restricted Countries
8					List Country
9	Spain			Registered	Germain	2%
10	Germain			Managed	Spain	5%
11	France			Operating	Australi	4%
12				UBO Nationality	Gibraltar	6%
13				UBO Residencey	USA	2%
14				Jurisdiction		7%
15				Licensed	Andorra	2%
16				Top 3 countries	Cile	3%
17					UK	7%
18					Malta	1%
19				Top 3 countries to pay	Canada	2%
20					Russia	8%
21					Serbia	1%
Sheet1

How about this condition? it will total column F because the first restricted countries is in background blue?

yes exactly so if the restricted countries appears in the blue cells column F counts normally. If the restricted countries appears in the yellow will give automatically 100 in G21

 

[RudRud]

Book2
	A	B	C	D	E	F	G
7	Restricted Countries
8					List Country
9	Spain			Registered	Serbia	2%
10	Germain			Managed	france	5%
11	France			Operating	Australi	4%
12				UBO Nationality	Gibraltar	6%
13				UBO Residencey	USA	2%
14				Jurisdiction		7%
15				Licensed	Andorra	2%
16				Top 3 countries	Cile	3%
17					UK	7%
18					Malta	1%
19				Top 3 countries to pay	Canada	2%
20					Russia	8%
21					Serbia	1%	100
Sheet1

Right Click Sheet1 -> view code, and then paste the code below

Sub test2()

Dim dict As New dictionary
dict.CompareMode = vbTextCompare

 [g:g].Value = ""
a = Range("a9:a" & Cells(Rows.Count, "A").End(xlUp).Row)

'Create an array then put into dictionary
For i = 1 To UBound(a, 1)
    dict.Add a(i, 1), ""
Next i

'Loop through Dynamic Column E
For Each ss In Range("e9:e" & Cells(Rows.Count, "E").End(xlUp).Row)
        If dict.Exists(ss.Value) Then
            If ss.Interior.Color = 9851953 Then 'Condition if background color is blue
                Range("g" & Cells(Rows.Count, "E").End(xlUp).Row) = Application.WorksheetFunction.Sum(Range("f:F"))
                Exit For
            ElseIf ss.Interior.Color = 65535 Then 'Condition if background color is yellow
                Range("g" & Cells(Rows.Count, "E").End(xlUp).Row).Value = 100
                Exit For
            End If
        End If
Next ss

End Sub

How about if the restricted countries not in either yellow background color or blue background color column?

 

[SanFranz]

Book2
	A	B	C	D	E	F	G
7	Restricted Countries
8					List Country
9	Spain			Registered	Serbia	2%
10	Germain			Managed	france	5%
11	France			Operating	Australi	4%
12				UBO Nationality	Gibraltar	6%
13				UBO Residencey	USA	2%
14				Jurisdiction		7%
15				Licensed	Andorra	2%
16				Top 3 countries	Cile	3%
17					UK	7%
18					Malta	1%
19				Top 3 countries to pay	Canada	2%
20					Russia	8%
21					Serbia	1%	100
Sheet1

Right Click Sheet1 -> view code, and then paste the code below

Sub test2()

Dim dict As New dictionary
dict.CompareMode = vbTextCompare

 [g:g].Value = ""
a = Range("a9:a" & Cells(Rows.Count, "A").End(xlUp).Row)

'Create an array then put into dictionary
For i = 1 To UBound(a, 1)
    dict.Add a(i, 1), ""
Next i

'Loop through Dynamic Column E
For Each ss In Range("e9:e" & Cells(Rows.Count, "E").End(xlUp).Row)
        If dict.Exists(ss.Value) Then
            If ss.Interior.Color = 9851953 Then 'Condition if background color is blue
                Range("g" & Cells(Rows.Count, "E").End(xlUp).Row) = Application.WorksheetFunction.Sum(Range("f:F"))
                Exit For
            ElseIf ss.Interior.Color = 65535 Then 'Condition if background color is yellow
                Range("g" & Cells(Rows.Count, "E").End(xlUp).Row).Value = 100
                Exit For
            End If
        End If
Next ss

End Sub

How about if the restricted countries not in either yellow background color or blue background color column?

Countries are in column E anyway. Could you please provide the excel formula as I use the spreadsheet and not vba
Thanks

 

[SanFranz]

Book2
	A	B	C	D	E	F	G
7	Restricted Countries
8					List Country
9	Spain			Registered	Serbia	2%
10	Germain			Managed	france	5%
11	France			Operating	Australi	4%
12				UBO Nationality	Gibraltar	6%
13				UBO Residencey	USA	2%
14				Jurisdiction		7%
15				Licensed	Andorra	2%
16				Top 3 countries	Cile	3%
17					UK	7%
18					Malta	1%
19				Top 3 countries to pay	Canada	2%
20					Russia	8%
21					Serbia	1%	100
Sheet1

Right Click Sheet1 -> view code, and then paste the code below

Sub test2()

Dim dict As New dictionary
dict.CompareMode = vbTextCompare

 [g:g].Value = ""
a = Range("a9:a" & Cells(Rows.Count, "A").End(xlUp).Row)

'Create an array then put into dictionary
For i = 1 To UBound(a, 1)
    dict.Add a(i, 1), ""
Next i

'Loop through Dynamic Column E
For Each ss In Range("e9:e" & Cells(Rows.Count, "E").End(xlUp).Row)
        If dict.Exists(ss.Value) Then
            If ss.Interior.Color = 9851953 Then 'Condition if background color is blue
                Range("g" & Cells(Rows.Count, "E").End(xlUp).Row) = Application.WorksheetFunction.Sum(Range("f:F"))
                Exit For
            ElseIf ss.Interior.Color = 65535 Then 'Condition if background color is yellow
                Range("g" & Cells(Rows.Count, "E").End(xlUp).Row).Value = 100
                Exit For
            End If
        End If
Next ss

End Sub

How about if the restricted countries not in either yellow background color or blue background color column?

it remains the normal count

 

[RudRud]

it remains the normal count

1. restricted countries if exist in column e backgroundcolor yellow then G21 = 100

this one 100 because e10 is france (restricted country) and in backgroundcolor yellow that's why output: 100

the code will start run from e9 to bottom, if found restricted country then will check the backgroundcolor (if yellow then 100, if blue then calculate all)

May i know what's the answer from this case? maybe im misunderstanding

 

[SanFranz]

Book2
	A	B	C	D	E	F	G
7	Restricted Countries
8					List Country
9	Spain			Registered	Serbia	2%
10	Germain			Managed	france	5%
11	France			Operating	Australi	4%
12				UBO Nationality	Gibraltar	6%
13				UBO Residencey	USA	2%
14				Jurisdiction		7%
15				Licensed	Andorra	2%
16				Top 3 countries	Cile	3%
17					UK	7%
18					Malta	1%
19				Top 3 countries to pay	Canada	2%
20					Russia	8%
21					Serbia	1%	100
Sheet1

Right Click Sheet1 -> view code, and then paste the code below

Sub test2()

Dim dict As New dictionary
dict.CompareMode = vbTextCompare

 [g:g].Value = ""
a = Range("a9:a" & Cells(Rows.Count, "A").End(xlUp).Row)

'Create an array then put into dictionary
For i = 1 To UBound(a, 1)
    dict.Add a(i, 1), ""
Next i

'Loop through Dynamic Column E
For Each ss In Range("e9:e" & Cells(Rows.Count, "E").End(xlUp).Row)
        If dict.Exists(ss.Value) Then
            If ss.Interior.Color = 9851953 Then 'Condition if background color is blue
                Range("g" & Cells(Rows.Count, "E").End(xlUp).Row) = Application.WorksheetFunction.Sum(Range("f:F"))
                Exit For
            ElseIf ss.Interior.Color = 65535 Then 'Condition if background color is yellow
                Range("g" & Cells(Rows.Count, "E").End(xlUp).Row).Value = 100
                Exit For
            End If
        End If
Next ss

End Sub

How about if the restricted countries not in either yellow background color or blue background color column?

please do you have the excel formula? I'm not able to convert the VBA one
Thank you!

 

[SanFranz]

1. restricted countries if exist in column e backgroundcolor yellow then G21 = 100

this one 100 because e10 is france (restricted country) and in backgroundcolor yellow that's why output: 100

May i know what's the answer from this case? maybe im misunderstanding

no 100 is the score that automatically should appears in G21 without any sum

 

[RudRud]

please do you have the excel formula? I'm not able to convert the VBA one
Thank you!

maybe need to wait other excel experts for formula, sorry since i can only provide through VBA code

 

[SanFranz]

maybe need to wait other excel experts for formula, sorry since i can only provide through VBA code

oh ok Rud no problem, thanks for your support, much appreciated anyway