**Question description:**

*This user has given permission to use the problem statement for this blog.*

I am trying to figure out how to use a sumif on SUBSTITUTE(A2,"*","~*") where A2 is a formula with 3371-4

Solved by M. Y. in 19 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
15/08/2018 - 08:05

Hello!

User
15/08/2018 - 08:05

hello

Excelchat Expert
15/08/2018 - 08:05

Welcome to got it pro!

Excelchat Expert
15/08/2018 - 08:05

Please feel free to show me some examples

User
15/08/2018 - 08:06

I am trying to figure out how to use a sumif on SUBSTITUTE(A2,"*","~*") where A2 is a formula with 1584-2

Excelchat Expert
15/08/2018 - 08:07

Ok

Excelchat Expert
15/08/2018 - 08:07

what does the sumif sums to?

Excelchat Expert
15/08/2018 - 08:07

And where the sum value come from

User
15/08/2018 - 08:08

you can see in the fuction what I'm trying to do

User
15/08/2018 - 08:08

The issue lies in the wildcard

User
15/08/2018 - 08:09

I changed the function at row 19

User
15/08/2018 - 08:09

I did that to make up for the numbers text output of the job

User
15/08/2018 - 08:09

I want this completely auto though

Excelchat Expert
15/08/2018 - 08:10

Ok, I see. I think the issue is that they are not be able to be recognized as numbers

Excelchat Expert
15/08/2018 - 08:11

Instead, they will be used as string

Excelchat Expert
15/08/2018 - 08:11

That's why the sumif won't work in this case

User
15/08/2018 - 08:11

ok

User
15/08/2018 - 08:12

what should be used in the sumif to recognize the different cases

User
15/08/2018 - 08:13

or what should be used in the first column to make it work

Excelchat Expert
15/08/2018 - 08:13

the sumif only can be used for number only

User
15/08/2018 - 08:13

I tried text() and value()

Excelchat Expert
15/08/2018 - 08:13

eliminate the dash

User
15/08/2018 - 08:13

yes

Excelchat Expert
15/08/2018 - 08:13

then it should be recognized as number

User
15/08/2018 - 08:14

I agree but I need to because these are job numbers

Excelchat Expert
15/08/2018 - 08:14

Right

Excelchat Expert
15/08/2018 - 08:14

But since they are string

Excelchat Expert
15/08/2018 - 08:14

there is no way to add or sum them as number

User
15/08/2018 - 08:15

I'm not adding the job number

User
15/08/2018 - 08:15

I'm adding a huge list of parts made for job numbers

User
15/08/2018 - 08:15

which is why I'

User
15/08/2018 - 08:15

I'm sumif()ing

User
15/08/2018 - 08:16

the rest of the formula is just setting the time between dates

Excelchat Expert
15/08/2018 - 08:16

You are doing sumif for column A correcT?

User
15/08/2018 - 08:16

no

User
15/08/2018 - 08:16

sumif is on another table

User
15/08/2018 - 08:17

I can't provide that data

Excelchat Expert
15/08/2018 - 08:17

Ok.

Excelchat Expert
15/08/2018 - 08:17

Do apologize, but I'm confused what you are trying to do from this worksheet then

User
15/08/2018 - 08:18

working

User
15/08/2018 - 08:23

nevermind, gtg

Excelchat Expert
15/08/2018 - 08:23

:-)

Excelchat Expert
15/08/2018 - 08:24

Well, very glad this issue can be solved for now

User
15/08/2018 - 08:24

no it wasn't

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*