VBA: Use a String Array as condition in SUMIFS

[dragonx]

Hey folks,

I try to sum a column of numbers, based on "string" conditions in anothe column e.g. I want to sum the values in column two which have an A or B in column one.

A 1
B 2
A 4
B 2
C 5
C 7

At the moment I try:

Sub ECS()
Dim EC() As String
EC = Array("DE", "DK", "EE")
Range("I21").Value = Application.WorksheetFunction.SumIfs(Worksheets("Ref").Range("L:L"), Worksheets("Ref").Range("C:C"), "=" & EC)
End Sub

I also tried just EC as a condition and defining EC as a variant but I always get the error "type not compatible" At the end my array will contain over 50 strings.

I found https://www.mrexcel.com/forum/excel-questions/735182-array-values-sumif-criteria-vba.html

but I can't modify it for my request. It also dont know how to handle the {} brackets in VBA and why they are used in this particular formular.

any help would be much appreciated.

kind regards,

dragonx

 

[Rick Rothstein]

Since you only have two conditions, why not just do it the direct way...

=SUMIF(A$1:A$6,"A",B$1:B$6)+SUMIF(A$1:A$6,"B",B$1:B$6)

 

[Akuini]

Here's one way to do it:

Sub a1076613a()
Dim ary, n
Dim x As Long
'https://www.mrexcel.com/forum/excel-questions/1076613-vba-use-string-array-condition-sumifs.html
ary = Array("A", "B", "C")
For Each n In ary
x = x + WorksheetFunction.SumIfs(Range("B:B"), Range("A:A"), n)
Next
Range("C1") = x
End Sub

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]A[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]B[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]C[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]D[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]A[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]E[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]C[/td][td]

7​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[dragonx]

@Rick Rothstein: Thank you for the hint but I misrepresented the problem as a little to simple, at the end my sumifs will contain many more conditions.
@Akuini: That was helpful =). It seemed to work first, but than I expanded my array to contain all conditions and it seems to not work properly. Could it be that the "loop" stops as soon as one array element is not in the column it checks? Because it compared my array and the column by hand it does only sum up for a part of the array elements.

Sub ECSALES()
Dim EC, n
Dim X, Y As Long
Y = CLng(Application.WorksheetFunction.EoMonth(Range("F2"), 0))
EC = Array("BE", "BG", "DK", "EE", "FI", "FR", "GR", "IE", "IT", "HR", "LV", "LT", "LU", "MT", "NL", "AT", "PL", "PT", "RO", "SE", "SI", "SK", "ES", "CZ", "HU", "GB", "CY")
For Each n In EC
X = X + Application.WorksheetFunction.SumIfs(Worksheets("Ref").Range("L:L"), Worksheets("Ref").Range("C:C"), n, Worksheets("Ref").Range("B:B"), "BE", Worksheets("Ref").Range("D:D"), "VAT", Worksheets("Ref").Range("R:R"), "<=" & Y)
Next
Range("I21") = X
End Sub

Not all part of the array EC are necessarly in the column that should be checked. Also, If we are at it I also need to reverse the "filter". Basically sumup all values for which the condition column does not contain the elements of the string array EC. As that will be all Non-EU countries of the world I can not enter them manually as a different array because they have not all been entered into the dataset and I do not know whether they will be entered using the correct codes so I want to go for all values where the condition column does not contain an array EC element.

Sorry, but I am a VBA beginner so I am justing getting into it.

Kind regards,

dragonxhttps://www.mrexcel.com/forum/members/rick-rothstein.html

 

[Akuini]

@Rick Rothstein: Thank you for the hint but I misrepresented the problem as a little to simple, at the end my sumifs will contain many more conditions.
@Akuini: That was helpful =). It seemed to work first, but than I expanded my array to contain all conditions and it seems to not work properly. Could it be that the "loop" stops as soon as one array element is not in the column it checks? Because it compared my array and the column by hand it does only sum up for a part of the array elements.


Not all part of the array EC are necessarly in the column that should be checked. Also, If we are at it I also need to reverse the "filter". Basically sumup all values for which the condition column does not contain the elements of the string array EC. As that will be all Non-EU countries of the world I can not enter them manually as a different array because they have not all been entered into the dataset and I do not know whether they will be entered using the correct codes so I want to go for all values where the condition column does not contain an array EC element.

Sorry, but I am a VBA beginner so I am justing getting into it.

Kind regards,

dragonx

Sorry, I don't quite understand what you mean.
Can you post some example to describe your problem.

 

[dragonx]

Sorry, I don't quite understand what you mean.
Can you post some example to describe your problem.

Please excuse, I noticed my array was just incomplete Your solution works great! ray:

And I noticed for the "opposite" case I can just take the sum of the entire column first and substract the amount I got through my SumIFs function :

Thank you very much for the help!

I am a little confused about the variable type however. I thought long would only store "whole" numbers, but my output includes decimals. That is what i want of course, but just to be on the safe site, should I Dim X as a Single? The numbers that have to be stored wont exceed a few million.

kind regards, dragonx

 

[Akuini]

Please excuse, I noticed my array was just incomplete Your solution works great! ray:

And I noticed for the "opposite" case I can just take the sum of the entire column first and substract the amount I got through my SumIFs function :

I thought long would only store "whole" numbers, but my output includes decimals.

kind regards, dragonx

You may declare the variable as Double.