SUMIF criteria in row not columns

[coop123]

Hi

I am sorry if this question has already been resolved but I cannot find a solution that meets my needs.

I have spread sheet where the criteria is in row 5 and sum range is also a row.

The problem is the number of columns containing the criteria will be different each time report is run.

In above example there are only 2 sets of data, next time there maybe 4 sets or just 1.

I need to be able to sum the data if there are 2 records or 4 or 1.

I hope I have explained suffeciently.

Thanks for any assistance you can give.

Coop123

 

[Anthony47]

Maybe you could set the formula on Sheet2 and extend the checked range to cope with max expansion; for example:

=SUMIF(Sheet1!C$5:AZ5;"TheHeader";Sheet1!C6:AZ6)

Bye

 

[coop123]

Hi
Sorry
I failed to mention that this is request is part of a macro.

coop123

 

[Anthony47]

Thus, to adapt to your main macro:

For I = 5 To MaxI
    Result = Application.WorksheetFunction.SumIf(Sheets("Sheet1").Range("C5").Resize(1, 70), "TheHeader", Sheets("Sheet1").Cells(I, "C").Resize(1, 70))
Next I

Bye

 

[coop123]

Hi Anthony47

I'm sorry but the code does not work. I get no result when applying. Am I missing something?

Macro would select in example above cell I6 then would sum values in that row where heading is PO + Req'n.

Thanks

coop123

 

[Anthony47]

Which is the full code of your macro? Could you share a sample workbook with some realistic datas to be used for testing?

Bye

 

[kvsrinivasamurthy]

1. You mean Column I is not same every time, Depending on the months last column will be decided. Thai if one more month data added then last column is "L". Is it so.
2. you need to insert formulas in the last column like the sample data above or do you need the total of all rows and columns with condition.

 

[coop123]

Hi

Cell with formula in is dependant on number of months which could be different each time. I want macro step to sum values in that row dependant on specified criteria in row 5. I want to then copy formula to last row containing data. (This last bit I can do it is just setting sumif formula I am struggling with. I have tried named range but cannot get that to work.

Thanks

coop123

 

[Alex Blakenburg]

See if this does what you are after:-

Sub AddFormula()

    Dim lastCol As Long, lastRow As Long, hdgRow As Long, firstRow As Long
    Dim sht As Worksheet
    
    Set sht = ActiveSheet
    hdgRow = 5
    firstRow = hdgRow + 1
    lastCol = sht.Cells(hdgRow, Columns.Count).End(xlToLeft).Column
    lastRow = sht.Cells(Rows.Count, "A").End(xlUp).Row
    
    '---------------------------------------------------------------------
    ' Optional code - uncomment if sometimes the Total Column doesn't exist
    ' and needs to be added
    '---------------------------------------------------------------------
'    If sht.Cells(hdgRow, lastCol).Value <> "PO + Req'n Total" Then
'        lastCol = lastCol + 1
'        sht.Cells(hdgRow, lastCol).Value = "PO + Req'n Total"
'    End If
    '---------------------------------------------------------------------
    
    sht.Range(Cells(firstRow, "C"), Cells(firstRow, lastCol)).FormulaR1C1 = _
        "= SUMIFS(RC3:RC" & lastCol - 1 & ",R" & hdgRow & "C3:R" & hdgRow & "C" & lastCol - 1 & ",""PO + Req'n"")"
        
End Sub

 

[coop123]

Hi Alex

The code does what I need. There is one problem, it puts the formula into all cells in the row removing any values and replacing with zero. I only need formula in the one cell. could you show me how to adjust your code to coreect this issue.

Thanks

coop123