Insert dynamic column reference into SUMIFS formula?

Do you have a header row in row 1? If so, does this contain the text values of the months (i.e. "April", "May", "June"), or the month/year, or are they dates (e.g. the first of the month) which might be formatted to display the month and/or year?

I can't see your workbook, so I don't know what you have, or how your data is laid out - it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

The short answer is that you can do this using INDEX/MATCH within the SUMIFS function to select the appropriate column, but I will need to see how your data is laid out before I can give you a specific formula.

Hope this helps.

Pete

Pete_UK - thanks for the response. I tried to upload the file, did it come through? Also, please note I made a change to the formula in the original post. I wasn't returning the column number in the previous one, so I fixed that

Pete_UK, one more time with an updated sumif formula. Sorry attempted to remove everything unnecessary, ended up ruining the formula in the process.

I'm not sure why you need to use SUMIFS, as column B cells in the Data and AccountData sheets all contain the same thing. This is how you could do it with a straightforward SUM formula:

=SUM(INDEX(AccountData!$E:$P,0,MATCH(TEXT(TODAY(),"mmm")&"Total",AccountData!$E$1:$P$1,0)))

so if you wanted to make this a SUMIFS formula, you would need to add the criteria after the sum-range, like this:

=SUMIFS(INDEX(AccountData!$E:$P,0,MATCH(TEXT(TODAY(),"mmm")&"Total",AccountData!$E$1:$P$1,0)),AccountData!B:B,Account!A5)

I've just added the criteria that you had in your first post, though it doesn't make sense in your sample workbook.

Hope this helps.

Pete

I've just noticed that you reposted a different workbook. Put the formula that I gave above into cell B3 of the Account sheet, and change the A5 to A3 at the end. You don't need B1 or B2.

Hope this helps.

Pete

Pete - thanks for the response, this works perfectly. I often catch myself making things more difficult than they need to be, and this was no exception!

Glad it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of any post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete