Thread: SUM IF Across Range - Possibly Using Index / Match, Sumproduct or Hlookup?

Hi there!

I am working with a colleague who doesn't have access to Excel 2007, so I can't use Sumif's to solve this, and I can't add a subtotal line (long story) to the data, which has me stumped on how, then, I can solve this problem. With the data attached (and that I attempted to paste below), I want a sum of lines 1-6 if the date at the top of the first table is the same as the date at the bottom section's month end date. (In other words, in the lower part of the data, under 9/30/11, it should equal $20k and in the part where it says 10/31/11 it should also equal $20k.

9/30/2011 10/31/2011 11/30/2011
Line 1 $10,000.00 $15,000.00
Line 2 $80,000.00
Line 3 $20,000.00
Line 4 $10,000.00
Line 5
Line 6


Week 9/9/2011 9/16/2011 9/23/2011 9/30/2011 10/7/2011 10/14/2011 10/21/2011 10/28/2011 11/4/2011
Month 9/30/2011 9/30/2011 9/30/2011 9/30/2011 10/31/2011 10/31/2011 10/31/2011 10/31/2011 11/30/2011
End of Month? Yes Yes
End of Month - date 9/30/2011 10/31/2011

Sum of lines 1 -6 $- $- $- $- $- $- $- $10,000.00 0


I've tried doing a sum if, but it will only sum the first row (line 1). I've also tried to do sumproduct or some sort of index/match combo, but can't quite get there. Any ideas? (Also, if it helps for indexing or something like that, I could change the "Line 1, Line 2, etc" to all just say "Line" (I think this would help if i could do SumifS, but alas I can't).

I REALLY appreciate any help!!!

Thank you, thank you!