CODEWITHSUNDEEP
excelsumifs
James
James

Reputation: 25

Excel using sumifs or another formula to calculate based off of column criteria

I am trying to write a excel formula that will calculate a data set based off of the column header for an area. Essentially, I am using it to help automate some of the mundane reconciliations that I do.

The data: I am wanting to search the column header for "Car_ERN" and sum that entire column. My formula I have been trying to get work is in two versions.

One version is using sumifs:

SUMIFS('Sum Payroll Dept Dump'!$Y$2:$CG$85,'Sum Payroll Dept Dump'!$Y$1:$CG$1,"ERN_CAR_AMT")

The other version is incorporating Sumproduct but I get a zero return. The "2" is searching a column that has multiple 2s in it because I want all rows for this criteria.

`SUMPRODUCT('Sum Payroll Dept Dump'!$Y$2:$CG$200,('Sum Payroll Dept Dump'!$Y$1:$CG$1='Filter Sheet'!A8)*('Sum Payroll Dept Dump'!A2:A200="2"))`

If there is a better way to do it without macros that would be much appreciated.

Thanks for the help!

Upvotes: 1

Views: 160

Answers (2)

tigeravatar
tigeravatar

Reputation: 26640

Formula only solution, assuming your desired column header to look for is in sheet 'Filter Sheet' cell A8 (using your example, that cell would contain "ERN_CAR_AMT"):

=SUMIF('Sum Payroll Dept Dump'!$A:$A,2,INDEX('Sum Payroll Dept Dump'!$Y:$CG,0,MATCH('Filter Sheet'!A8,'Sum Payroll Dept Dump'!$Y$1:$CG$1,0)))

Upvotes: 1

Matt Cremeens
Matt Cremeens

Reputation: 5151

You could make a user-defined function (UDF) in VBA.

function sumByHeader(header as string, headerRng as range) as Double
Dim hdr as Range
Dim i as Long
Dim thisSum as Double
for each hdr in headerRng
    if hdr.value = header then
        thisSum = 0
        for i=2 to ActiveSheet.UsedRange.Rows.Count
            thisSum = thisSum + cells(i, hdr.Column)
        next i
    end if
next i
sumByHeader = thisSum
End Function

This function loops through your header row (headerRng) and looks for a particular header (header). When it finds it, it sums ever cell under it in the column it is in, starting from row 2 and going to the end of the last cell of your used range.

Then in a cell in your worksheet, you can use the function like you would any other

=sumByHeader("Car_ERN", A1:Z1)

Upvotes: 0

Related Questions