Date Value not working in formula

[MrsFraser07]

Hi everyone! This is probably simple, but it has me stumped. Any suggestions are appreciated. Here's my formula:
=SUMIFS('AccuMap Dump'!$AH$3:$AH$241,'AccuMap Dump'!$M$3:$M$241,">=$K$1",'AccuMap Dump'!$N$3:$N$241,"<5",'AccuMap Dump'!$N$3:$N$241,">0")

$K$1 is a date (value is 1/1/2023) that is calculated from another date as: =DATE(YEAR('AccuMap Dump'!M1),MONTH('AccuMap Dump'!M1),1)

What I want the SUMIFS formula to do is sum the number of wells in column AH that are greater than or equal to 1/1/2023 (date that is calculated in $K$1). The formula works if I replace $K$1 with ">=1/1/2023" but I don't want to have to manually type that in each time I change my dataset. How do I get Excel to recognize the date value in $K$1?

 

[Fluff]

It should be ">="&$K$1 Otherwise it's looking for the text $K$1 rather than the cell value

 

[ExceLoki]

you could also bypass the K1 cell and use the formula from K1 like this:

=SUMIFS('AccuMap Dump'!$AH$3:$AH$241,'AccuMap Dump'!$M$3:$M$241,">="(DATE(YEAR('AccuMap Dump'!M1),MONTH('AccuMap Dump'!M1),1)),'AccuMap Dump'!$N$3:$N$241,"<5",'AccuMap Dump'!$N$3:$N$241,">0")

but i'd prolly just do as fluff pointed out

 

[Fluff]

use the formula from K1 like this:

You've got the " in the wrong place.

 

[MrsFraser07]

you could also bypass the K1 cell and use the formula from K1 like this:

=SUMIFS('AccuMap Dump'!$AH$3:$AH$241,'AccuMap Dump'!$M$3:$M$241,">=(DATE(YEAR('AccuMap Dump'!M1),MONTH('AccuMap Dump'!M1),1))",'AccuMap Dump'!$N$3:$N$241,"<5",'AccuMap Dump'!$N$3:$N$241,">0")

but i'd prolly just do as fluff pointed out

Thanks! Your formula works except for one small change I made: ">="(DATE(YEAR('AccuMap Dump'!M1,MONTH('AccuMap Dump'!M1),1))
I moved the " from the end to after the = and it works great!

 

[MrsFraser07]

It should be ">="&$K$1 Otherwise it's looking for the text $K$1 rather than the cell value

Thanks! Worked great. I knew I was missing something simple! I just couldn't see it. Thanks!

 

[ExceLoki]

fixed, thanks

 

[Fluff]

Glad we could help & thanks for the feedback.