All solutions FormulaSUMIF Expert Solution – How to Use a SUMIF Formula

Excel - How to Use a SUMIF Formula - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc