Question description:
This user has given permission to use the problem statement for this
blog.
I am looking for a formula to SUM column L, if Column A matched the relevant date and column C matches the name
Solved by Z. Y. in 16 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
17/08/2018 - 12:47
Hi, did you get my query
Excelchat Expert
17/08/2018 - 12:47
Hello, yes, I did. :)
Excelchat Expert
17/08/2018 - 12:47
So you want to get the SUM of records that meet the date and name criteria, right?
User
17/08/2018 - 12:48
Thats correct. I want to return a sum of the activity on a set date by a set person.
Excelchat Expert
17/08/2018 - 12:48
Alright we can definitely help with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
17/08/2018 - 12:48
I'll be able to properly provide an accurate solution for you if you can provide an accurate example data. Are you able to share your file?
User
17/08/2018 - 12:49
I can share a file.
Excelchat Expert
17/08/2018 - 12:49
Please do share your file using the clip icon next to this chat.
Excelchat Expert
17/08/2018 - 12:49
Let me know once you've uploaded it.
User
17/08/2018 - 12:52
how do I upload
Excelchat Expert
17/08/2018 - 12:52
CLick the paperclip icon next to this chat.
User
17/08/2018 - 12:52
done it
[Uploaded an Excel file]
Excelchat Expert
17/08/2018 - 12:52
Then navigate to your file, select it and click OK.
Excelchat Expert
17/08/2018 - 12:52
Thank you. Give me a moment to download and analyze your file. I may have a few question.
Excelchat Expert
17/08/2018 - 12:53
Alright, which sheet and which cell should I be looking at?
User
17/08/2018 - 12:53
I want the fomula in cell b6 on tab BAU Template
User
17/08/2018 - 12:54
the data is in template quotes
User
17/08/2018 - 12:54
date is colum a
User
17/08/2018 - 12:54
name on column c
Excelchat Expert
17/08/2018 - 12:54
I see what you mean, so for B6 it's BO Quotes
and August 1, right?
User
17/08/2018 - 12:55
yep
Excelchat Expert
17/08/2018 - 12:55
Okay, one minor problem.
User
17/08/2018 - 12:56
Sorry the name is B5 "Paul MacGowan"
Excelchat Expert
17/08/2018 - 12:56
In your quotes sheet, the date format is date and time while your row 5 of BAU templates, it is short date.
Excelchat Expert
17/08/2018 - 12:56
Yes, that's what I meant too, sorry.
User
17/08/2018 - 12:56
I can change the date format
Excelchat Expert
17/08/2018 - 12:57
We can still extract the date from the Date Time but it would result to a much more complicated formula so what I'd suggest is to change the format of the date to short date so it matches.
Excelchat Expert
17/08/2018 - 12:57
The time doesn't matter anyway as they are all 12:00 AM.
User
17/08/2018 - 12:57
Ive changed it on mine
Excelchat Expert
17/08/2018 - 12:57
Alright, I see you already have a SUMIF formula, the solution I'm going to propose is similar, it's called SUMIFS.
Excelchat Expert
17/08/2018 - 12:57
It allows multiple criteria.
User
17/08/2018 - 12:58
sounds good
Excelchat Expert
17/08/2018 - 12:58
In fact, I'd recommend that you always use SUMIFS instead as it doesn't really have any drawback and it can be used for multiple and single criteria, and you only have to remember 1 syntax instead of remembering both SUMIF and SUMIFS.
Excelchat Expert
17/08/2018 - 12:58
With sumif you are ALWAYS stuck with single criteria.
Excelchat Expert
17/08/2018 - 12:58
Anyway, give me a few minutes.
Excelchat Expert
17/08/2018 - 01:00
Use this formula in B6:
Excelchat Expert
17/08/2018 - 01:00
=SUMIFS(quotes!$L:$L,quotes!$C:$C,'BAU Template'!$A$5,quotes!$A:$A,'BAU Template'!B$5)
Excelchat Expert
17/08/2018 - 01:00
Then drag to the right.
Excelchat Expert
17/08/2018 - 01:01
Unfortunately, you can't fully test the formula due to not having any other data, but if you have other names in there, you can test it out by changing the name in A5.
User
17/08/2018 - 01:01
Thanks mate
Excelchat Expert
17/08/2018 - 01:02
You are welcome. Glad to have helped!
Excelchat Expert
17/08/2018 - 01:02
Would there be anything else that I can help you with regards to the original question?
User
17/08/2018 - 01:02
Thats me, appreciate the help, will definitely think about Got It in the future.
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.
