VLOOKUP With SUMIF

[jski]

I'm attempting to add numbers in a column (H) based on a date (column I) and then take that result and look it up in a table (RateTable) which will then multiply that result by a percentage. Here's the formula:

=((VLOOKUP(SUMIF($H:$H,"3/1/2015",$I:$I),RateTable,3,TRUE)*(SUMIF($H:$H,"3/1/2015",$I:$I))))


I:I = 100,000
H:H = 3/1/2015
RateTable = 10%
Result = 10,000

The reason I'm using TRUE is that the add value may fall between two variables in one tier of the RateTable. Reformatting the date yields nothing. I'm really at a loss as to why this isn't working as the logic seems correct. My result returns zero with the formula above. I would appreciate a second set of eyes to help determine what the issue is. Many thanks in advance.


jski

 

[Khalid NGO]

Hi jski,
can you post a sample of your file with dummy data?

Regards,

 

[jski]

Unfortunately, no. My company has blocked that ability. Attached is a test file. Highlighted areas are the formulae in question. Thanks.

 

[NARAYANK991]

Hi ,

The first point is that you cannot use entire column references in this case , since your formula is in the same column that you are using within the formula , which means you are generating a circular reference.

Second , the syntax of the SUMIF formula is different from the way it has been entered ; try this :

=VLOOKUP(SUMIF($I$9:$I$29,"1/4/2015",$H$9:$H$29),RateTable,3,TRUE)*(SUMIF($I$9:$I$29,"1/4/2015",$H$9:$H$29))

This formula is to be entered in H60.

Narayan

 

[jski]

Thanks Narayan. That did it! I was using the entire column as the range will expand beyond row 29 as new data is entered. How would one accommodate for that within the formula?