Help with SUMIFS & INDEX/MATCH Formulas

I need to first find the column number where the number 1 resides on the "Lookup Value -->" row.

Next, I need to shift two columns to the right, which would be "4/2019 C" and sum the values for Client B,

I have tried various combinations of SUMIFS, INDEX(MATCH()), etc.,but with my last attempt, I received #INVALID VALUE with the following formula:

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

Please help! Thank you.

Find more posts tagged with

Sheets

Formulas

Comments

Paul Newcome

Can you provide a screenshot with some dummy data in it that shows what you want this to look like?

Dan Seidler

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

Paul Newcome

Ok. So in summary... You want to find the 1, move two columns to the right, and then sum everything in that column?

Sorry if it seems I am asking for a lot of clarification. I just want to make sure we are getting this right. Don't want to waste a lot of time coming up with a "solution" that in all reality doesn't accomplish what you need.

Dan Seidler

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

Paul Newcome

Ok. I've got it now. I have a few ideas but will need a little time for testing and working the details out. I'll get back to you unless someone else is able to come up with a solution before I can.

Paul Newcome

Ok. So I am going to suggest a helper column. For this example I'll name it "Current". I am also going to assume the final column is [12/2019 C]. I am also going to move the row containing the Lookup Value to row 1 since I can't see your current row numbers. You can adjust this to match what you need.

In the Current Column, enter the following:

=INDEX([3/2019 A]@row:[12/2019 C]@row, 1, MATCH(1, [3/2019 A]$1:[12/2019 C]$1, 0) + 2)

This will pull the number from the corresponding row that is two columns to the right of the column containing 1. Note: It is important to keep the row reference in the MATCH function locked using the $. This way you can dragfill the rest of the way down the Current Column.

From there you can use a basic

=SUMIFS(Current:Current, Client:Client, "Client B")

to sum ever cell in the Current column that has "Client B" in the Client column on the same row.

Dan Seidler

Paul,

This is perfect! Thank you so much for your assistance!

Thanks,

Dan

Paul Newcome

Happy to help!

Out of curiosity... What are you using to move the 0, 1, and 2 across the columns?

Dan Seidler

Since I am not using the Date format columns for these, I simply put 3 in [3/2019 A] for March and 2019 in [3/2019 C].

=IF(YEAR(TODAY()) < [3/2019 C]@row, 12 - (12 + [3/2019 A]@row - MONTH(TODAY())), IF(YEAR(TODAY()) = [3/2019 C]@row, (12 + [3/2019 A]@row - MONTH(TODAY())) - 12, [3/2019 A]@row - (12 + MONTH(TODAY()))))

I know it still needs some work. I am concerned with the yearly rollover. Any suggestions?

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…