CODEWITHSUNDEEP
vbaexcel
Student1001
Student1001

Reputation: 111

Sumif with column index instead of alphabet form range

I am using 

  =SumIf(ws!A:A,"Some Text", ws!G:G)

to add some data to a new spreadsheet.

I am wondering if there is a way to convert A or G to 1 or 7, or some other workaround that will give me the same effect as the Sumif function using column index instead of the alphabetical form.

This seems crucial for me because my next cell will be:

sumif(ws!A:A, "Some Text", ws!I:I)
sumif(ws!A:A, "Some Text", ws!K:K)

and so on.

I wish to loop this in intervals of two columns (as shown in the example) but I don't know how to do it using the alphabet form of range. Some help will be greatly appreciated.

Upvotes: 1

Views: 203

Answers (3)

user3598756
user3598756

Reputation: 29421

edit: edited after realizing OP asked for formula every uneven column...

assuming "NewSheet" as the name of the worksheet you want to add data to via a SumIf() function referencing ws worksheet, here's two alternative codes

  1. "array" approach

    Option Explicit

Sub main()
Dim ws As Worksheet
Dim myArr As Variant
Dim i As Long
Dim formulaStrng As String

Set ws = Worksheets("OldSheet") '<-- change as per your actual "ws" name
formulaStrng = "=SumIf(" & ws.Name & "!C1,""Some Text""," & ws.Name & "!C)"
With Worksheets("NewSheet").Range("A1:NC1")
    ReDim myArr(1 To .Columns.Count)
    For i = 1 To .Columns.Count
        If 2 * Int(i / 2) <> i Then myArr(i) = formulaStrng 
    Next i
    .value = myArr
End With
End Sub

  2. "helper row" approach

    Sub main2()
Dim ws As Worksheet

Set ws = Worksheets("OldSheet")
With Worksheets("NewSheet").Range("A1:NC1")
    .FormulaR1C1 = "=SumIf(" & ws.Name & "!C1,""Some Text""," & ws.Name & "!C)"
    With .offset(1) '<--| helper row. assuming row 2 is "free". should it not be, simply adjust the offset
        .FormulaR1C1 = "=if(2*int(column()/2)<>column(),1,"""")" '<-- mark "uneven" columns with "1"
        .value = .value '<-- get rid of formulas
        .SpecialCells(xlCellTypeBlanks).EntireColumn.Clear ''<--  clear "even" colums
        .ClearContents ''<-- clear helper row
    End With
End With

End Sub

both approaches can be speeded-up adding the following code at the beginning:

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

and the the following code by their end:

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Upvotes: 1

L42
L42

Reputation: 19727

Here's using VBA:

Sub concious()
    Dim i As Integer, j As Integer: j = 0 
    For i = 0 To 10 Step 2 '<~~ depends how many columns you wish to populate
        With Sheet2
            Sheet1.Range("A1").Offset(0, j).Formula = _
                "=SUMIF(" & .Columns("A:A").Address(, , , True) & _
                ",""Some Text""," & .Columns("G:G").Offset(0, i).Address(, , , True) & ")"
        End With
        j = j + 1
    Next
End Sub

Well, Sheet1 is where you put the formula, Sheet2 is your reference sheet.
You can replace Sheet1 with Sheets("NameofYourActualSheet") or your actual sheet codename.

This is the formula version:

=SUMIF(Sheet2!$A:$A,"Some Text",OFFSET(Sheet2!$G:$G,,(COLUMN()-1)*2))

This part COLUMN()-1 depends where you want to put your formula.
It works only if formula starts in Column A at Sheet1.

Upvotes: 1

Tim Edwards
Tim Edwards

Reputation: 1028

=SUBSTITUTE(ADDRESS(1,x,4),"1","")&":"&SUBSTITUTE(ADDRESS(1,x,4),"1","")

x would be your column

You'd then have to convert that from text to a range:

=SUMIF(INDIRECT(SUBSTITUTE(ADDRESS(1,x,4),"1","")&":"&SUBSTITUTE(ADDRESS(1,x,4),"1","")),"B",A:A)

Upvotes: 0

Related Questions