I have a spend value in column C that is associated with a Costs Centre in column A and a nominal code in column B.
I then have a series of costs centres in cells E1:E6 and list of nominal codes in F1:F6.
I want the sum of spend for all combinations of Cost Centre and Nominal Code entered in E1:F6.
I know I can do this with multiple Sumifs, but is there a neater way to do this?
i.e. =Sum(Sumifs(C:C,A:A,E1,B:B,F1),Sumifs(C:C,A:A,E1,B:B,F2),Sumifs(C:C,A:A,E1,B:B,F3),Sumifs(C:C,A:A,E1,B:B,F4),Sumifs(C:C,A:A,E1,B:B,F5),Sumifs(C:C,A:A,E1,B:B,F6),Sumifs(C:C,A:A,E2,B:B,F1)... Sumifs(C:C,A:A,E6,B:B,F6)
Any help gratefully appreciated.
I then have a series of costs centres in cells E1:E6 and list of nominal codes in F1:F6.
I want the sum of spend for all combinations of Cost Centre and Nominal Code entered in E1:F6.
I know I can do this with multiple Sumifs, but is there a neater way to do this?
i.e. =Sum(Sumifs(C:C,A:A,E1,B:B,F1),Sumifs(C:C,A:A,E1,B:B,F2),Sumifs(C:C,A:A,E1,B:B,F3),Sumifs(C:C,A:A,E1,B:B,F4),Sumifs(C:C,A:A,E1,B:B,F5),Sumifs(C:C,A:A,E1,B:B,F6),Sumifs(C:C,A:A,E2,B:B,F1)... Sumifs(C:C,A:A,E6,B:B,F6)
Any help gratefully appreciated.