SUMIF not working when referencing a cell with a formula

[cmacnab]

Hi, I'm building a new worksheet with a sumif function. One of the cells the formula refers to is itself a formula and the sumif is not working. When I remove this part of the sumif, the formula works so I know this part is the issue. This is the formula I'm using:
=SUMIFS('Punch Report'!$J:$J,'Punch Report'!$A:$A,$A6,'Punch Report'!$U:$U,$C6,'Punch Report'!$B:$B,">=09/27/2021",'Punch Report'!$B:$B,"<=10/03/2021")

The part that is causing the formula to not return a result is 'Punch Report'!$U:$U. Column U on the Punch Report is a Vlookup function that returns a value from another tab. I'm not sure why this is not working as I have another spreadsheet that I do the exact same function, just different columns, and it works fine.

Anyone have any ideas why this is?

 

[jasonb75]

That part of the formula will not be the issue, the problem will be that the vlookup results in column U are not the same as the value in C6. Without seeing the vlookup formula, the result it returns, and the content of C6, it is going to be difficult to identify the reason without a lot of guesswork.

 

[Dave Patton]

Do you require over a million rows?
Is the lookup returning Text or Values?
Does the formula work if the date criteria is a reference to cells?
You can format the dates to your preference.
N.B. You can post a concise extract of your data with the forum's tool XL2BB.

T202110a.xlsm
	A	B	C	D						J
1
2										100
3										20210927
4										20211003
5
6	AA		X
7	AA	20210924								20
8	AA	20211001								40
9	AA	20211002								60
10	AA	20211004								80
1c

Cell Formulas
Range	Formula
J2	J2	=SUMIFS($J7:$J10,$A7:$A10,$A6,$U7:$U10,$C6,$B7:$B10,">="&J3,$B7:$B10,"<="&J4)

 

[cmacnab]

Figured out the issue. Had the wrong codes in the table that that the lookup formulas were referring to, lol. Thank you for your help.