Index/Match/Sumproduct multiple rows and columns

问题:

I am trying to get year to date total for a particular account. Suppose we're in July now. The year to date total for account 5340 should be 2800.

In the formulas below, AT29=5340 and AT28=7

I have tried

=SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$4:AU$24,0),7)) but that only gives me the first row found.

=SUMPRODUCT((AU4:AU24=AT29)*AV4:BG24) but that gives me the whole year.

=SUMPRODUCT((AU4:AU24=AT29)*INDEX(AV4:BG24,,AT28)) but that just gives me the sum for July only.

=SUMPRODUCT((AU4:AU24=AT29)*(SUM(INDEX(AV4:AV24,,1):INDEX(AV4:BG24,,AT28)))) but that gives me something I have no idea what it is XD

回答1:

Because your data is ordered on the primary index column, you can do this with a single SUM function overtop of a single OFFSET function.

To determine the area you will be summing, start at the top left corner above and to the left of your data - let's say that's A1 (I can't quite tell from your examples how your page is actually set up). You will want to move down the number of rows until you MATCH the account you're looking for. You'll want to move to the right 1 column, because you will always be starting in January. Then you go for as many rows as there are matches for that account number within your data. You go for as many columns as there are months in your test timeline. Together, this looks something like:

=SUM(OFFSET(A1,MATCH(A12,A1:A9,0),1,COUNTIFS(A2:A9,A12),A13))

Note: This assumes your accounts go from A2:A9, and that you've typed up a specified account number in cell A12, and that you've calculated the number of months in the year to date in cell A13.

TL;DR: sum a 2D box that starts at the top left with the first instance of the particular account, goes down for as many rows as there are instances of that account, and goes to the right for as many columns as there are months in the year to date.

回答2:

IF the numbers in AU are in order the following nonvolatile formula will work:

=SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$‌​4:AU$24,0)+COUNTIF(AU$4:AU$24,AT29)-1,7))

Using the COUNTIF() will extend the rows the correct number of rows.