**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.*