SUMIFs Formula

[mcmuney]

I have a structure of data that looks something like this:

Type GroupA GroupB GroupC Category
Apple 5 2 3 REVENUE
Apple 2 9 6 REVENUE


On an output file, I have a similar structure, but only unique rows:

Type GroupA GroupB GroupC
Apple (on these cells I'd like to utilize SUMIFs; there would be 3 formulas here (see below))

=SUMIFS(B$2:D$3,$A$2:$A$3,"Apple",$E$2:$E$3,"REVENUE",$B$1:$D$1,"GroupA")
=SUMIFS(B$2:D$3,$A$2:$A$3,"Apple",$E$2:$E$3,"REVENUE",$B$1:$D$1,"GroupB")
=SUMIFS(B$2:D$3,$A$2:$A$3,"Apple",$E$2:$E$3,"REVENUE",$B$1:$D$1,"GroupC")

But this formula doesn't seem to work. Can't seem to do do intersection using SUMIFs. Am I doing something wrong?

[6StringJazzer]

I can't reverse engineer your formulas to figure out what you're trying to do. But based on your description, is it this:

In the output report, provide one line of output for each unique combination of Type and Category (for example, one row for Apple and REVENUE.) You want to sum the figures for GroupA, B, C. In that case I think you want these formulas:

=SUMIFS(B:B,A:A,"Apple",E:E,"REVENUE")
=SUMIFS(C:C,A:A,"Apple",E:E,"REVENUE")
=SUMIFS(D:D,A:A,"Apple",E:E,"REVENUE")

(I haven't tested this.)

Your original formulas tested for whether the row contains "GroupA", etc., but it never does; that's the heading.

[mcmuney]

I've used what you're suggesting and it works; however, I'm trying to avoid specify certain columns, instead, I'd like to utilize a range by giving it a criteria. Just like we're doing with REVENUE, I'd like it to specify the range and tell it GroupA, B or C. The formula doesn't seem to work with a range. The reason I need to use a range is because, the columns will expand from month to month and having a range will require less future maintenance.

Is there any other way?

[daddylonglegs]

SUMIFS requires all ranges to be the same size (and shape) - you need to use SUMPRODUCT, try

=SUMPRODUCT(B$2:D$3,($A$2:$A$3="Apple")*($E$2:$E$3="REVENUE")*($B$1:$D$1="GroupA"))

[mcmuney]

This does work; however, it's quite strange that it works even if the very last criteria is off. For example: If I change *($B$1:$D$1="GroupA") to *($AB$1:$AD$1="GroupA"), assuming that B1=GroupA and AB1=GroupA, it will pull the sum from B2. Is this normal?

[daddylonglegs]

Well, yes....I would expect this to be used in the suggested way, so that it sums the values at the intersection of the matching row headers/column headers......

.....but it doesn't have to work that way, it's just matrix multiplication, so B1:D1 could be replaced by any three horizontal cells, E99:G99 for instance, and if the first of those is "GroupA" then the first column of the sumrange will potentially be summed (depending on the other criteria being fulfilled).

This also applies to any other range in the formula. The only requirement is that the range sizes match, you're multiplying a 3 cell horizontal array by a 2 cell vertical array so the sum range must also be 3x2

[mcmuney]

Thanks for explaining it, makes sense now.