Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I am trying to write a formula that states - If column J = "List" then it will give the total in column N, if column J + "Buy" then it will give 1/2 the total of column N
Solved by S. J. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 22/11/2017 - 07:46
Welcome to excel got it pro
Excelchat Expert 22/11/2017 - 07:46
How are you feeling today?
User 22/11/2017 - 07:47
I am good thank you
Excelchat Expert 22/11/2017 - 07:47
I understand you want to show total in column N if column J is "list". else if J is buy then N is half of total
Excelchat Expert 22/11/2017 - 07:47
right?
User 22/11/2017 - 07:48
Yes - the formula will be in column Q
Excelchat Expert 22/11/2017 - 07:49
a bit more to clarify that I have all info to write the formula.
Excelchat Expert 22/11/2017 - 07:49
what is the total column?
User 22/11/2017 - 07:49
Ok - In Q is where i need the total
Excelchat Expert 22/11/2017 - 07:49
or can you show a sample row?
User 22/11/2017 - 07:50
I have an amount in column N - if column J contains List then the fully amount in column N needs to show in column Q
Excelchat Expert 22/11/2017 - 07:50
Great
Excelchat Expert 22/11/2017 - 07:50
that's all I need
User 22/11/2017 - 07:50
if in column J contains Buy, then 1/2 the amount needs to show from column N in column Q
Excelchat Expert 22/11/2017 - 07:50
gimme couple of minutes to write the formula.
User 22/11/2017 - 07:50
ok great
Excelchat Expert 22/11/2017 - 07:52
=IFS(J2="List",N2,J2="Buy",N2/2)
Excelchat Expert 22/11/2017 - 07:52
here's the formula assuming the first row is row 2.
Excelchat Expert 22/11/2017 - 07:53
change the 2 associated with J and N's accordingly to your data.
User 22/11/2017 - 07:53
not sure I understand that?
User 22/11/2017 - 07:53
my first row is 23
Excelchat Expert 22/11/2017 - 07:54
then the formula should be as follows
User 22/11/2017 - 07:54
YES it totally works
Excelchat Expert 22/11/2017 - 07:54
=IFS(J23="List",N23,J23="Buy",N23/2)
Excelchat Expert 22/11/2017 - 07:54
cool
Excelchat Expert 22/11/2017 - 07:54
you are easy to teach.
Excelchat Expert 22/11/2017 - 07:54
:)
Excelchat Expert 22/11/2017 - 07:55
anything else?
Excelchat Expert 22/11/2017 - 07:56
If not please close the session by pressing "end session" button and leave positive feedback on my service.
User 22/11/2017 - 07:56
let me make sure I'm getting everything else hold on
Excelchat Expert 22/11/2017 - 07:56
sure, take your time. I'm here as long as you need me.
User 22/11/2017 - 08:01
one last question - my formula needs to state:
Excelchat Expert 22/11/2017 - 08:01
hmm, what is it?
User 22/11/2017 - 08:02
If Jan!$k23:$k63,"Joe Wengerd) that 's the first part, but I also need to add to it - if column N says "list" then it will give me total count
User 22/11/2017 - 08:03
so if column k23-k63 say Joe, and List it will give me a count - that way i can tell how many in january are for joe & list
Excelchat Expert 22/11/2017 - 08:03
ah, can you share the previous formula? the part you shared doesn't really mean anything.
User 22/11/2017 - 08:05
ok this is a new formula I need. I have 12 sheets - jan - dec.... i have a separate sheet that is doing a total count for the year - I need this formula to tell me if k23-k63 has joe, and column l has list it will start giving me a count
Excelchat Expert 22/11/2017 - 08:07
any row from k23-k63 would be sufficient? the initial question was relatively easy and solvable without seeing the data but the later question is a bit difficult.
Excelchat Expert 22/11/2017 - 08:07
I may need to look at the data.
Excelchat Expert 22/11/2017 - 08:07
If it's possible please share the file.
User 22/11/2017 - 08:08
i don't really have the data - i'm creating this from scratch
Excelchat Expert 22/11/2017 - 08:08
Can you share the file with me?
User 22/11/2017 - 08:10
i dont have a file
Excelchat Expert 22/11/2017 - 08:10
where are you working then?
Excelchat Expert 22/11/2017 - 08:12
Well try this now
Excelchat Expert 22/11/2017 - 08:12
=IFS(MATCH("Joe Wengerd",Jan!$K23:$K63,0),1,KJ23="List",N23,J23="Buy",N23/2)
Excelchat Expert 22/11/2017 - 08:17
Hello!

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