Search code examples
excelindexingexcel-formulasumifs

Dynamic Sumifs across several columns adding some and skipping some

I have a budget from Jan to Dec, populated with monthly figures for different business units and different business functions (although some business functions are the same). However, when creating a monthly P+L where I'd like to compare ACT vs BGT, I'd like to have the flexibility of obtaining the YTD total of a specific budget line by business unit & function depending on the month we are in.

I could use the following formula, but I am looking for something shorter and lighter as the formula has to be used in several other cells other than just on the dashboard. This is so that the excel workbook does not become heavy and bloated.

<=+IF(AND(C23="Apr",B23="ZWE | Maintenance")=TRUE,+SUMIFS(C3:C19,B3:B19,B24,A3:A19,B23)+SUMIFS(D3:D19,B3:B19,B24,A3:A19,B23)+SUMIFS(E3:E19,B3:B19,B24,A3:A19,B23)+SUMIFS(F3:F19,B3:B19,B24,A3:A19,B23),"")>

Screen shot of the formula

In another post on this forum, the below function would work beautifully but I do not know to add another sum field

Excel Sumifs with dynamic columns

This is the formula used in the post =F3:F6, LAMBDA(x,SUM(XLOOKUP(G1,): XLOOKUP (I1, B2:D2, B3:D6) *(x=A3:A6))))

In my case, I'd like the BYROW (F3:F6... portion to be for my two conditions, i.e., "ZWE | Maintenance" & "Maintenance | Cleaning", and IF possible, the ability to add a third condition as well. What formula could I use?

Business Unit Business Activity Jan Feb Mar Apr
ZWE - Maintenance Maintenance - Back-Charging 27 27 27 25
ZWE - Maintenance Maintenance - Check Out 52,437 52,785 52,344 49,085
ZWE - Maintenance Maintenance - Cleaning 84,059 84,616 83,909 78,685
ZWE - Maintenance Maintenance - House Keeping 138,763 139,682 138,516 129,892
ZWE - Maintenance Maintenance - Manpower 3,865 3,891 3,858 3,618
ZWE - Maintenance Maintenance - Marble Cleaning 2,101,339 2,115,262 2,097,603 1,967,009
ZWE - Maintenance Maintenance - MEP 4,797 4,829 4,789 4,490
ZWE - Maintenance Maintenance - Pest Control 37,643 37,892 37,576 35,237
RSA - Maintenance Maintenance - Back-Charging 53,078 53,430 52,984 49,685
RSA - Maintenance Maintenance - Check Out 1,230,497 1,238,650 1,228,310 1,151,837
RSA - Maintenance Maintenance - Cleaning 5,419 5,455 5,410 5,073
RSA - Maintenance Maintenance - House Keeping 2,009 2,023 2,006 1,881
RSA - Maintenance Maintenance - Manpower 496 499 495 464
RSA - Maintenance Maintenance - Marble Cleaning 4,978 5,011 4,969 4,660
RSA - Maintenance Maintenance - MEP 3,396,485 3,418,989 3,390,448 3,179,362
RSA - Maintenance Maintenance - Pest Control 16,539 16,649 16,510 15,482
Solution

  • This formula works in traditional versions e.g. Excel 2013. The formula must be confirmed as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

    =SUM(OFFSET(C1:F1,MATCH(1,(A2:A17=B21)* 
(B2:B17=B22),0),0,1,MATCH(C21,C1:F1,0)))

    Dynamic Sumifs across several columns adding some and skipping some