none
Sumif on multiple column headings RRS feed

  • Question

  • Hi. I have a table with months going across from column BA4:CQ4 and headings going down from rows D5:D90. The Data is in fields BA5:CQ90. In other words months going across and titles going down.

    I want the total for quarter ending 31 December 2016 and where the row value is oxford. I have tried several formula and getting nowhere.  Below is the formula I thought would give me the answer:

    =SUMIFS('Planner - Budget'!BA5:CQ90,'Planner - Budget'!BA4:CQ4,"<="&A17,'Planner - Budget'!D5:D90,"Oxford") (where A17 is 31/12/16

    It gives me #VALUE!

    Someone must have come across this before?

    Monday, November 28, 2016 11:38 AM

All replies

  • Hello,

    The forum is for Outlook related questions. I'd suggest asking Excel specific questions on the Excel for Developers forum instead. 


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Monday, November 28, 2016 2:03 PM
  • Ignoring the possibility that you have a bunch of months in there that meet your DATEVALUE("12/31/2016") criteria (e.g. "2/14/2015"), the primary difficulty is that 'SumIfs' wants to work in either columns or rows, not a grid as you are expecting.

    You can see this by eliminating the second, 'Oxford' criteria, leave the sum_range as is & you get the #Value! error. Setting your sum_range to only a single row, !BA5:CQ5, and you get an answer. Conversely, you can also eliminate the first 'date' criteria, leaving the sum_range to !BA5:CQ90 you get the #Value!. Change the sum_range to !BA5:CQ5 and voila, you have an answer for 'Oxford'.

    I can see 3 possible solutions:

    (1) Insert a column that calculates a eligible result: " =If(D5<>"Oxford",0,sumifs(('Planner - Budget'!BA5:CQ5, 'Planner - Budget'!BA4:CQ4,"<="&A$17, 'Planner - Budget'!BA4:CQ4,">"&(A$17-92))
    ... you will have to tweak that last term to avoid getting September dates, but that's easy...

    (2) Insert a row to contain a subtotal for each month, then use data/filters to filter specifically Column D. THat gets you the monthly subtotal for all f the months. You can then use a similar SUMIfs function as in solution (1).

    (3) Use a pivot table. You can just use the month columns you want, assign the D column heading to filters or rows.

    -MainSleuth

    Tuesday, November 29, 2016 8:43 PM
  • Hi,

    Please visit SUMIFS function:

    Use the same number of rows and columns for range arguments.

    The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.

     

    You could try the suggestion from MainSleuth.

    In my opinion, you could also write an UDF to sum the value.

    You could refer the sample below and write your UDF to meet your requirement.

    Option Base 1
    Function testFun(sumrng As Range, rowRng As Range, rowCri As Range, colRng As Range, colCri As Range) As Integer
    Dim rowAry() As Integer
    Dim i, j As Integer
    i = 1
    j = 1
    For Each cell In rowRng
    If cell.Value = rowCri.Value Then
    ReDim Preserve rowAry(i)
    rowAry(i) = cell.Row
    i = i + 1
    End If
    Next
    
    Dim colAry() As Integer
    For Each cell In colRng
    If cell.Value = colCri.Value Then
    ReDim Preserve colAry(j)
    colAry(j) = cell.Column
    j = j + 1
    End If
    Next
    
    For i = 1 To UBound(rowAry)
    For j = 1 To UBound(colAry)
    testFun = testFun + Cells(rowAry(i), colAry(j)).Value
    Next j
    Next i
    End Function
    

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 1, 2016 6:22 AM
    Moderator