Sumif with right and/or replace

[drluke]

I am trying to use sumif =SUMIF('Sales'!$A$1:$A$15237,'Monthly'!A1,'Sales'!$D$1:$D$15237)

The problem is that the data in Monthly A1 is text written as yyyy/mm which I cannot convert to a value unless I use something like RIGHT or REPLACE:

=RIGHT('Sales'!A1,1)+0 or

=REPLACE('Sales'!A1,1,5,"")

I don't know how to:

(a) apply either of RIGHT or REPLACE to a range of cells, and
(b) build this argument into my SUMIF.

Any advice would be appreciated.

 

[steve the fish]

Ok so what appears in column A of sales? In Monthly A1 if you click into the cell what do you see in the formula bar?

 

[drluke]

There is no formula. It's text eg. 2018/3

 

[steve the fish]

Ok so what appears in column A of sales?

And this?

 

[Aladin Akyurek]

There is no formula. It's text eg. 2018/3

If 'Sales'!$A$1:$A$15237 consists of true dates, try:

=SUMIFS('Sales'!$D$1:$D$15237,'Sales'!$A$1:$A$15237,">="&('Monthly'!A1&1)+0,'Sales'!$A$1:$A$15237,"<="&EOMONTH('Monthly'!A1&1,0))

Is this what you are after?

 

[steve the fish]

If true dates maybe:

=SUMIFS(Sales!$D$1:$D$15237,Sales!$A$1:$A$15237,">="&REPLACE(Monthly!A1,1,5,)&"/"&LEFT(Monthly!A1,4),Sales!$A$1:$A$15237,"<"&EOMONTH(REPLACE(Monthly!A1,1,5,)&"/"&LEFT(Monthly!A1,4),0)+1)

 

[drluke]

Thank you both. The 'dates' in Monthly are not true dates

 

[Aladin Akyurek]

Thank you both. The 'dates' in Monthly are not true dates

If $A$1:$A$15237 of Sales are true dates, the suggestion I made should work.

 

[steve the fish]

You need to say what these dates are then...

 

[drluke]

Eg. Monthy A1= 2018/1
In B1-B6 I used the formulas below to check A1
=ISNUMBER(A1) result = #VALUE!
=DATEVALUE(A1) result = #VALUE!
=ISERROR(DATEVALUE(A1)) result = TRUE
=ISTEXT(A1) result = TRUE
=CELL("format",A1) result = G

<colgroup><col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>