Question description:
This user has given permission to use the problem statement for this
blog.
If I want to write a formula that states" "If this name shows up K24, and this word shows up in J24 pull this number from M24" I can creat that formula - however I need to drag this down so it pulls from row 24 - 60 - is there way to do this?
Solved by F. D. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
27/10/2017 - 03:49
Hello, I understand that you need help in dragging your if formula down so it works for the other rows, right?
Excelchat Expert
27/10/2017 - 03:51
Are you here?
Excelchat Expert
27/10/2017 - 03:53
Hello, if you can read this but are unable to reply, please refresh your browser.
Excelchat Expert
27/10/2017 - 03:54
Otherwise, I'll just have to explain the solution based on the little information that we have.
Excelchat Expert
27/10/2017 - 03:54
You an always drag a formula downwards by selecting the cell you want to drag, clicking and holding the small box at the bottom right and dragging it.
Excelchat Expert
27/10/2017 - 03:55
The cell references gets automatically adjusted if they are not locked.
Excelchat Expert
27/10/2017 - 03:55
If you want to lock the cell references, for example if you want to keep m24 as m24 even if you drag the formula down, you just lock the column and row by adding the $
Excelchat Expert
27/10/2017 - 03:55
So instead of m24 you type $m$24
User
27/10/2017 - 03:56
im here
Excelchat Expert
27/10/2017 - 03:56
Hello, please be advised that we have a very limited amount of time per session and we have about 10 minutes left on this one so we'll have to get moving.
User
27/10/2017 - 03:57
that's find
Excelchat Expert
27/10/2017 - 03:57
Based on your description, you just need to drag the formula down? Are you encountering issues doing that?
User
27/10/2017 - 03:58
i'm just trying to figure out how to add to this formula: =if(K24="Nick",If(J24="list",M24,"0")) - how do i make this pick up rows 25-60?
Excelchat Expert
27/10/2017 - 03:58
Can you elaborate on what you meant by pickup 25-60?
Excelchat Expert
27/10/2017 - 03:59
Or better yet, are you able to upload a file with your data?
Excelchat Expert
27/10/2017 - 03:59
And then give me an example using that file?
User
27/10/2017 - 04:00
i can't upload - how do I add more rows to this formula, so when adding my information on my sheet, as I use more rows it contain mores rows than just 24
Excelchat Expert
27/10/2017 - 04:00
What's the value of m24?
Excelchat Expert
27/10/2017 - 04:00
Is it a number?
User
27/10/2017 - 04:00
this value will be different every time
Excelchat Expert
27/10/2017 - 04:01
Do you want to sum all the Nicks and list?
User
27/10/2017 - 04:02
i want all the items that have "list" in column J with Nick's name in column K total his amount from column M
Excelchat Expert
27/10/2017 - 04:02
Yes, if that's the case then you don't need an if statement.
Excelchat Expert
27/10/2017 - 04:02
What you need is SUMIFS.
Excelchat Expert
27/10/2017 - 04:02
Let me show you.
User
27/10/2017 - 04:02
how do i write this then
Excelchat Expert
27/10/2017 - 04:02
Please look at the Dcoument preview to the right. J24
User
27/10/2017 - 04:02
ok
Excelchat Expert
27/10/2017 - 04:03
N30 has the formula.
Excelchat Expert
27/10/2017 - 04:04
If you'll look at the sample data set, only the first row and the 4th row satisfies both your condition.
Excelchat Expert
27/10/2017 - 04:04
And their respective values are 1 and 4
Excelchat Expert
27/10/2017 - 04:04
Which makes the result of the formula 5.
Excelchat Expert
27/10/2017 - 04:04
=sumifs(M:M,K:K,"Nick",J:J,"list")
Excelchat Expert
27/10/2017 - 04:04
Paste that formula in any cell that is not in M, K or J column.
Excelchat Expert
27/10/2017 - 04:05
A gentle reminder that since you've extended the session: Please ensure that you end the session properly later or else I'll be stuck here waiting for the timer to expire and I'll be unable to help others.
User
27/10/2017 - 04:05
YES!!! That works
Excelchat Expert
27/10/2017 - 04:06
Great!
Excelchat Expert
27/10/2017 - 04:06
I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert
27/10/2017 - 04:06
Please do not forget to click the End Session otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
27/10/2017 - 04:06
i for sure do!!! I've been trying to figure this out for 2 days!!!!!!
User
27/10/2017 - 04:06
can i then copy this formula and put in other cells for different names?
Excelchat Expert
27/10/2017 - 04:06
Feel free to simply contact us again for any help with Excel so you won't have to spend too much time. We can help you with it.
Excelchat Expert
27/10/2017 - 04:07
Yes, you can change the ones inside the " "
Excelchat Expert
27/10/2017 - 04:07
Those are your criteria.
Excelchat Expert
27/10/2017 - 04:07
Basically M:M is what you need to sum
Excelchat Expert
27/10/2017 - 04:07
K:K is the column that checks "Nick" or any name
Excelchat Expert
27/10/2017 - 04:08
J:J is the column where it checks for "list" or any word you want.
Excelchat Expert
27/10/2017 - 04:08
Would there be anything else that I can help you with regards to the original question?
User
27/10/2017 - 04:08
amazing - thank you!
Excelchat Expert
27/10/2017 - 04:08
You are welcome. I hope the service I provided is worthy of a 5 star rating. :)
User
27/10/2017 - 04:08
It's easy when I understand where/why/what/how
Excelchat Expert
27/10/2017 - 04:09
Are you still with me?
Excelchat Expert
27/10/2017 - 04:09
Please do not forget to click the end session button, otherwise, I'd be stuck here until the session timer expires and be unable to help others like you.
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.
