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]

Did you try either of the formulas provided?

 

[drluke]

I tried both. Result = 0 for both

 

[Aladin Akyurek]

[TABLE="width: 194"]
<colgroup><col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="width: 194, bgcolor: transparent, colspan: 3"]Eg. Monthy A1= 2018/1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]In B1-B6 I used the formulas below to check A1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]=ISNUMBER(A1) result = #VALUE![/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]=DATEVALUE(A1) result = #VALUE![/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]=ISERROR(DATEVALUE(A1)) result = TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]=ISTEXT(A1) result = TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]=CELL("format",A1) result = G[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

The question does not involve 'Monthly'!A1. Rather: $A$1:$A$15237 of Sales.

What does

=ISNUMBER(Sales!$A$1)

return?

 

[drluke]

Apologies - that was a typo - I meant Sales not Monthly. In Monthly I have eg. for Jan in A1=1 and it is a number

 

[Aladin Akyurek]

Apologies - that was a typo - I meant Sales not Monthly. In Monthly I have eg. for Jan in A1=1 and it is a number

A1 of Monthly is 1, yes?

What is:

=ISNUMBER(Sales!$A$1)

 

[drluke]

Monthly A1=1
Sales A1=2018/1

ISNUMBER('Monthly'!$A$1)=TRUE
ISNUMBER('Sales'!$A$1)=#VALUE!

 

[steve the fish]

You need to be careful using 1 to represent January. Your sheet can never span more than one complete calendar year or your SUMIF will be incorrect.

 

[Aladin Akyurek]

Monthly A1=1
Sales A1=2018/1

ISNUMBER('Monthly'!$A$1)=TRUE
ISNUMBER('Sales'!$A$1)=#VALUE!

Is this then what we want?

[a]
=SUMIFS('Sales'!$D$1:$D$15237,'Sales'!$A$1:$A$15237,2018&"/"&'Monthly'!A1)

Or this?


=SUMIFS('Sales'!$D$1:$D$15237,'Sales'!$A$1:$A$15237,"*"&"/"&'Monthly'!A1)

 

[drluke]

Thank you very much! Both of those work

 

[Aladin Akyurek]

Thank you very much! Both of those work

That's great. Thanks for the update.