Error in Excel VBA code using SumIfs/Index/Match

I originally posted my code stating I couldn't figure out why I was getting an error. Since then I think I was able to trace it specifically to the fact that multiple columns are being used to look up the proper date and use that column as as the criteria range. I would really appreciate any help anyone can give.


If I replace the Indexing variable with one exact column to use as the Range (WSRev.Range("$AK:$AK")) then the RegionFcstAmt1 works fine and returns the correct value. Below works.

Please Login or Register  to view this content.

However, I need the formula to look up the correct column based on the date and use that as the criteria range. If I use the Sheets Range with more than one column it doesn't work either. Below does not work.

Please Login or Register  to view this content.

And I'm trying to use the Index/Match but that is not working either.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I shall do it for you this time.)

You had tried, but all you had done was type # at either end of the code! Better to select the text that is code and then click on the # symbol on the toolbar.