Help with SUMIFS & INDEX/MATCH Formulas

Hi Paul,

Please let me know if you cannot see the posted dummy data that I posted with my inquiry.

I have three columns for each month; the first is for user input, the second is for projected hours, and the last one is for actual hours.

With the example posted, Client B during the month of April (4/2019 C) has a sum of 27 (2+25).

I have already written a formula to compare each month to the current month.  On the "Lookup Value -->" row, 0 = current month, 1 = next month, 2 = 2 months from current month.  This means, on 4/1/2019, Column "4/2019 A" will change from 1 to 0.

Due to the "Lookup Value -->" row changing every month, the column to sum will continue to change.  The formula I am trying to write is to find the "Lookup Value -->" with the value of 1.  Once it sees this value, I want it to look 2 columns to the right and sum up the data which meets the criteria in my posted formula (below).

Rationale behind my posted formula:

=SUMIFS(INDEX(Client1:[5/2019 C]4, MATCH(1, Client5:[5/2019 C]5, 0) + 2), Client1:Client4, "Client B")

Indexing all of the data, find "1" on the "Lookup Value -->" row.  Then look over 2 columns.  Then sum the data which equates to "Client B".

The formula I am currently using to acquire this result is absolutely HUGE, hence I was hoping to simplify the formula.  This Smartsheet keeps crashing due to multiple uses of my large formula.

Thanks for inquiring! - Dan

Hi Paul. 

No, we are not looking to sum the entire column.  In this example, we are only looking to sum the values of Client B.  We will then use the same formula to look for Client A & Client C in different cells.

Thanks again. - Dan