Search code examples
excelexcel-formulaaggregatevlookupsumifs

SUMIFS with column selection in Excel

Suppose I have a table like this:

Branch Type Jan Feb Mar
b1 Other 100 120 130
b1 Other 10 20 30
b1 Rent 5 6 6
b2 Lease 2 2 5
b2 Rent 1 1
b2 Other 2 4

I want to aggregate data and get a new table for the branch b1:

Jan Feb March
Other 110 140 160
Rent 5 6 6
Lease 0 0 0

I have tried to run sumifs, but it doesn't just work because I want to choose the appropriate column based on month values. Non-matching ranges obviously give the #VALUE error.

I have tried to select the right columns within sumifs from the source table using index, match but I get #N/A

When I just select the right column in the source manually, sumifs returns zeros even though the source table has numbers.

Can I sum cells from the source based on multiple conditions across rows and columns? Preferably without macros involved.

This seems to be solving a similar issue but I do not understand what the formula does and how to apply it in my case.

Solution

  • You can do this like this:

    enter image description here

    Formula in cell H4 is:

    =SUMIFS(CHOOSE(MATCH(H$3;$C$1:$E$1;0);$C$2:$C$7;$D$2:$D$7;$E$2:$E$7);$A$2:$A$7;"b1";$B$2:$B$7;$G4)

    For this formula to work the month names must be exactly the same in both headers (Notice in your example you use March and Mar).

    Also, notice I'm using "b1" raw value to filter Branch but you can link this to a cell and calculate for b1, b2 and so on.

    Anyways, Just a suggestion, consider using Pivot Tables to get all data ypu want with a few clicks:

    enter image description here