SUMIFS not working to search column for any value that contains specific date

[vijayjeyapalan]

Hi there,

I am in need of help with a SUMIFS function I'm trying to use.

I am trying to sum the total quantity of products sold in a column if it matches the SKU and date in the corresponding column.

In this first image you can see the function:
Sheet 1: https://postimg.org/image/xi73c6fij/

=SUMIFS(input!R:R,input!V:V,'CD Test'!A13,input!P:P,"*"&L1&"*")

The first criteria works and what I'm doing there is: Matching the sku in referencing the SKU in cell A13 (sheet 1), reference the range in the second sheet (sheet 2) for column V

Sheet 2: https://postimg.org/image/d9jpqgg7f/

The second criteria doesn't work and what I'm doing there is: Matching the date in L1= 2017-03-10 (sheet 1), reference the range in the second sheet (sheet 2) in column P

IF both are true then sum the values in column R that correspond true to both criteria only.

The issue with the second criteria is that I have a single date 2017-03-10 (that is formatted as date, and is True if you do =isnumber(L2)) and I've added wildcard "*"&L2&"*" because when it looks in the range in Sheet 2 in column P the values are formatted as "2017-03-10 11:41:05 AM" (if you do =isnumber(any value in P) is TRUE) and I've tried to reformat to Date as well and doesn't return anything.

If anyone knows how I can fix this that would be very helpful!

 

[Jonmo1]

You can't use wildcards on numeric values (Dates are just numbers)

Is L1 a real date as well, or a text string?
What does =ISNUMBER(L1) return

Does L1 also contain the time like in P ?
What does =INT(L1)=L1 return

 

[vijayjeyapalan]

=ISNUMBER(L1) returns true

L1 does not contain time like in P

=INT(L1) returns the same value back:[TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-03-10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Try

=SUMIFS(input!R:R,input!V:V,'CD Test'!A13,input!P:P,">="&L1,input!P:P,"<"&L1+1)

 

[vijayjeyapalan]

That worked! thank you so much

 

[Jonmo1]

You're welcome.