SUMIFS to only include results with a valid date

[Badvgood]

Good morning all,

I am stuck on part of a SUMIFS formula where one of the criteria is that one of the ranges must contain a valid date and not / /

=SUMIFS(Ext_price,SOCo,V2,Sale_type,"E",Type_code,$W$1,Last_sales_date,">01/01/1900") is where I think the problem is.

See attached, in the yellow cells for where this formula is. The format of the date cannot be changed so I have to work with what I have!

Thanks in advance

BVG

[etaf]

column S - are NOT dates , they are text and so a > date will not work

also sales type E only has A, L Z type codes

Also W2 is fixed with $
so W$2 so it moves to pickup the codes
and also V2 is not fixed - so that needs to be fixed
$V2

then it works OK

I used Text to columns to change the dates to real dates

hope this helps

[Badvgood]

column S - are NOT dates , they are text and so a > date will not work

also sales type E only has A, L Z type codes

Also W2 is fixed with $
so W$2 so it moves to pickup the codes
and also V2 is not fixed - so that needs to be fixed
$V2

then it works OK

I used Text to columns to change the dates to real dates

hope this helps

Good morning and thank you for your help (we are almost neighbours looking at your location!)

Any idea how I can work with what I have to include or exclude the ones that have a text field that looks like a date and the ones that are / / ?

Changing text to columns is not an option in the master file...

Thanks BVG

[etaf]

i have edited and added the file
Does that work ?

Ok, not sure where you are UK

heres a line V4 etc same function , but without the date filter added, so you can compare

[Bo_Ry]

Please try
=SUMIFS(Ext_price,SOCo,$V2,Sale_type,"E",Type_code,W$1,Last_sales_date,"> a")

There is a space before "> a"

or
=SUMIFS(Ext_price,SOCo,$V2,Sale_type,"E",Type_code,W$1,Last_sales_date,"<>*/")

[Badvgood]

i have edited and added the file
Does that work ?

Ok, not sure where you are UK

heres a line V4 etc same function , but without the date filter added, so you can compare

Hi, thank you so much for being persistent with this, I appreciate it greatly.

Unfortunately, I can not use the Text to Columns D/M/Y function in the main sheet. Already tried this with ">01/01/1900" and hit the problem of it not being a date format.

I have found that by using this I can see the ones that look like dates from the ones that are not dates. =(ISERR(DATEVALUE(S2))) Is there some way of including something like this in the formula?

Very close to you in West Sussex, if you put the kettle on, it would still be hot enough by the time I got there!

Thanks BVG

[Badvgood]

Please try
=SUMIFS(Ext_price,SOCo,$V2,Sale_type,"E",Type_code,W$1,Last_sales_date,"> a")

There is a space before "> a"

or
=SUMIFS(Ext_price,SOCo,$V2,Sale_type,"E",Type_code,W$1,Last_sales_date,"<>*/")

Perfect, thank you. Both options work perfectly in the main dataset.

Thanks BVG

[davsth]

=SUMPRODUCT(Ext_price,(SOCo=V2)*(Sale_type="E")*(Type_code=$W$1)*(LEN(Last_sales_date)>6)) is an other option

[etaf]

Very close to you in West Sussex, if you put the kettle on, it would still be hot enough by the time I got there!

not if west wittering beach open without needing a booking