Home > OS >  EXCEL - SUMIFS up 2 dimensional range
EXCEL - SUMIFS up 2 dimensional range

Time:11-20

I'm trying to sum up a range of columns AND rows in EXCEL/*

Trivial Data example:

A B C D E F
Colour Size Jan Feb Mar Apr
red S 1 0 2 0
red M 3 2 1 3
green M 4 3 5 3
geren L 1 0 0 2
blue S 2 1 2 1

Desired Outcome:

Color Small Medium/Large
red 3 9
green 0 18
blue 6 0

I've tried for the red/small box:

=SUMIFS(C2:F5, A2:A5, "red", B2:B5, "S")

but it's returning a #VALUE error.

If the sum range is C2:C5 it works and returns a 1 but:

  1. I need all the columns
  2. The order of the rows is HIGHLY variable
  3. The number of columns is controlled by a variable so I actually have an INDEX in there that I've excluded from this because it would just confuse things.

How can I get this to work?

/* I have inherited this process and I'm not allowed to use anything fancier than EXCEL formula

CodePudding user response:

You need to switch to SUMPRODUCT in such cases:

=SUMPRODUCT((A2:A5="red")*(B2:B5="S")*C2:F5)

Or even just SUM:

=SUM((A2:A5="red")*(B2:B5="S")*C2:F5)

though the latter may require committing with CTRL SHIFT ENTER, depending on your version of Excel.

  • Related