Google Sheets - sumif using condition on row and column

Question

I have a spreadsheet that looks like the following

TABLE 1

ID/Month | May | June | July | August | September | October

ID101    | 30  | 50   | 50   | 80     | 20        | 60

ID201    | 20  | 30   | 10   | 40     | 30        | 50

ID101    | 10  | 50   | 60   | 80     | 70        | 20

ID301    | 20  | 80   | 70   | 40     | 40        | 70

ID101    | 30  | 70   | 80   | 50     | 90        | 50

ID301    | 80  | 20   | 30   | 20     | 60        | 20

TABLE 2

ID    | Date      | Value

ID101 | July      | ?

ID201 | September | ?

ID301 | June      | ?

? is the sum of the values in TABLE 1 if the IDs matches, and if the row of dates are less than or equal to the dates specified in TABLE 2.

So

• for ID101 | July | ? I need to find the sum of values in row ID101 in TABLE 1 and May/June/July columns
• for ID201 | September | ? I need to find the sum of values in row ID201 in TABLE 1 and May/June/July/August/September columns

How do I do a sumif like an index match table where I can look up conditions in column (IDs) and rows (less than or equal to dates)

Answer 1

You can use SUMPRODUCT function:

=SUMPRODUCT((J2=$A$2:$A$7)*(MONTH(K2&1)>=MONTH($B$1:$G$1&1))*$B$2:$G$7)

To convert your month names to correct number using MONTH(K2&1) formula you must specify a United States locale in the spreadsheet settings

Answer 2

You'll want to do three things:

• Un-pivot your table ("wide" to "long") so that each value is in its own row, identified by an ID and a Month (this is a little tricky)
• Give your month a numeric value (the MONTH() function comes in handy)
• Use SUMIFS to check the ID column and the Month column.

Here's a working sample: Google Sheets link