Asked by:
Sumif on multiple column headings
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?
 Moved by Chenchen LiModerator Tuesday, November 29, 2016 3:11 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.

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$1792))
... 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

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.