Sumif index and match & Google sheets

Question

I have a bit of a dilemma with the Countif Index and Match function when used for multiple tables. (on Google Sheets) Basically what I want to do is the following:

I use a simple table as an example - I want to count the sum total of values with similar descriptions (Like A, B etc) over different tables. If I use "=SUMIF($D$2:D$10,"A",Index($D$2:$E$10,Match("A",$D$2:$D$10,0),2))" for A where the Match is used for the [row], A gives a correct answer, but B, C etc is incorrect.

On the other hand, when I use "=SUMIF($D$2:D$10,"B",Index($D$2:$E$10,0,Match("B",$D$2:$D$10,0)))" where the Match is used for [column], B is correct but everything else is wrong.

I'm not sure where my mistake is and why I cant get the formula to work for all variables? And then across all tables too. Please assist :D

Answer 1

I'd suggest you use a query instead! =query(D:E,"select D,sum(E) group by D") should work. And then you wouldn't need separate formulas for each question.

Answer 2

The following formula

=SUMIF($D$2:D$10,"A",Index($D$2:$E$10,Match("A",$D$2:$D$10,0),2))

resolves to

=SUMIF($D$2:D$10,"A",Index($D$2:$E$10,1,2))

which in turn resolves to

=SUMIF($D$2:D$10,"A",$E$2)

But the documentation both for Google sheets and Excel says that SUMIF must be called as follows

SUMIF(range, criterion, [sum_range])

So what happens if [sum_range] is just a cell reference like $E$2, and not a range? The answer is that it is expanded to match the criteria range so what you get is:

=SUMIF($D$2:D$10,"A",$E$2:E$10)

This works perfectly and you get the sum of the rows in column E where column D contains "A".

But what happens if you have

=SUMIF($D$2:D$10,"B",Index($D$2:$E$10,Match("B",$D$2:$D$10,0),2))

This resolves to

=SUMIF($D$2:D$10,"B",$E$3)

So what you now get is

=SUMIF($D$2:D$10,"B",$E$3:$E$11)

which looks like this

with the net result that you get the rows in E3:E10 corresponding to the ones in column D which contain a B. These are the 2nd and 4th rows, which contain 20 and 30, giving you a total of 50.

The answer to the second part of the question is that anything other than B in the formula

=SUMIF($D$2:D$10,"B",Index($D$2:$E$10,0,Match("B",$D$2:$D$10,0)))

will not give you the second column of D2:E10, so it will fail.