All solutions COLUMNS Expert Solution – Excel COLUMN Problems

Excel - COLUMN Function Problem - Expert Solution

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

I would like to have a diagram in a new sheet, where the horizontal axis is the days, as they are in column DX. Each day shall show the sum of all unique leads of that day, and I would like to be able to check via a box of checkboxes, which facilities are shown, the facilities are in column BC.
Solved by I. A. in 45 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/08/2018 - 03:17
Hi
User 13/08/2018 - 03:17
Hello, I DO have a sheet
User 13/08/2018 - 03:17
Hello there!
Excelchat Expert 13/08/2018 - 03:17
Please share
User 13/08/2018 - 03:18
[Uploaded an Excel file]
User 13/08/2018 - 03:18
There we are. What is your name?
Excelchat Expert 13/08/2018 - 03:18
I can't disclose that... it's against the policy
User 13/08/2018 - 03:19
ok, no problem
Excelchat Expert 13/08/2018 - 03:19
opening the sheet now
User 13/08/2018 - 03:20
Sure.
Excelchat Expert 13/08/2018 - 03:20
I understand on the x axis there would be column DX
Excelchat Expert 13/08/2018 - 03:21
what would be on y axis
User 13/08/2018 - 03:21
You are right, Sorry.
Excelchat Expert 13/08/2018 - 03:21
And BC would be the filtering option through check boxes
User 13/08/2018 - 03:21
Yes.
User 13/08/2018 - 03:22
Actually, I just realized DX should be grouped by day
Excelchat Expert 13/08/2018 - 03:22
Okay
Excelchat Expert 13/08/2018 - 03:22
sure, will do that
Excelchat Expert 13/08/2018 - 03:22
y-axis?
User 13/08/2018 - 03:23
, the time is irrelevant, but the day would be great. So that if I select with the checkboxes say facilities "Seefeldstrasse 69" and "Unspezifisch", it would each day show the sum of all leads for those two facilities.
User 13/08/2018 - 03:23
horizontally should be the dates.
Excelchat Expert 13/08/2018 - 03:23
got it
User 13/08/2018 - 03:23
It has been a long time since school, Sorry: - )
Excelchat Expert 13/08/2018 - 03:23
no problem
Excelchat Expert 13/08/2018 - 03:24
please tell how to get sum of lead
User 13/08/2018 - 03:24
I filtered it by column S, so that every line is one lead, as uniquely identified by their email address in column S.
Excelchat Expert 13/08/2018 - 03:25
So, count of column S would give me the sum of leads?
User 13/08/2018 - 03:26
No. Every line is one lead. Each lead has a date. Ideally, I would like to have the number of leads per day. So to create that sum, per day, that is something I need done.
Excelchat Expert 13/08/2018 - 03:27
got it
Excelchat Expert 13/08/2018 - 03:27
working on .it
User 13/08/2018 - 03:27
cool
User 13/08/2018 - 03:27
thank you
User 13/08/2018 - 03:33
Are you still there and can I help with anything? The clock up there is at 4min, will we be disconnected if it runs out?
Excelchat Expert 13/08/2018 - 03:33
Here it is
[Uploaded an Excel file]
Excelchat Expert 13/08/2018 - 03:33
I attached the solution
Excelchat Expert 13/08/2018 - 03:34
I created an additional sheet
Excelchat Expert 13/08/2018 - 03:34
There's the solution
Excelchat Expert 13/08/2018 - 03:34
For filtering, there are two option provided
Excelchat Expert 13/08/2018 - 03:35
one in the top row and another a slicer
User 13/08/2018 - 03:36
I am happy to pay for an hour if you can finish this with me- I see we are close but either I do not get it or something is not working
Excelchat Expert 13/08/2018 - 03:36
what do you feel is not working?
Excelchat Expert 13/08/2018 - 03:36
I am happy to help
User 13/08/2018 - 03:37
can you open it "here"?
User 13/08/2018 - 03:37
that would be easier
User 13/08/2018 - 03:37
"here" being on the right ->
User 13/08/2018 - 03:37
:-)
User 13/08/2018 - 03:37
ah, now I understand a bit more
Excelchat Expert 13/08/2018 - 03:37
yes... i am not sure if i can
Excelchat Expert 13/08/2018 - 03:38
ut trying
Excelchat Expert 13/08/2018 - 03:38
*but
User 13/08/2018 - 03:39
I understand columns A and B now,
User 13/08/2018 - 03:39
OH, and in the field you made, I can select more than one entry, got it
User 13/08/2018 - 03:39
there is a spike at the end each time- is there a problem if i remove the SUM at the end of column B?
Excelchat Expert 13/08/2018 - 03:40
yes... i will remove and resend it
User 13/08/2018 - 03:40
thank you!
Excelchat Expert 13/08/2018 - 03:41
Here it is
[Uploaded an Excel file]
User 13/08/2018 - 03:42
hm, if I select e.g. only Konradstrasse, the diagram only fills half of the diagram area-
User 13/08/2018 - 03:43
is that a problem with my excel or because only on those days there were leads, e.g. the diagram is set to show 500ish days, and there were only leads on say 250 of them?
Excelchat Expert 13/08/2018 - 03:43
yes, it is because of that only
Excelchat Expert 13/08/2018 - 03:43
do you use windows or mac?
User 13/08/2018 - 03:43
mac
User 13/08/2018 - 03:44
understand- but that is just aesthetics. thank you so much sir or madam! this was REALLY helpful! how do I pay you?
Excelchat Expert 13/08/2018 - 03:45
It will be taken by the portal, once you end the session
Excelchat Expert 13/08/2018 - 03:45
Does this solve the problem?
User 13/08/2018 - 03:45
99% yes, the last 1% I can do myself. Thank you so much!
Excelchat Expert 13/08/2018 - 03:46
you are welcome
Excelchat Expert 13/08/2018 - 03:46
What is the 1% ?
Excelchat Expert 13/08/2018 - 03:46
can try and solve that also
User 13/08/2018 - 03:46
I would like an option to group leads by week and month,
User 13/08/2018 - 03:47
but as it is not a graph that shows each day no matter what, but only the days where there were leads, I guess that is a bit complicated to add.
User 13/08/2018 - 03:47
group leads in the diagram, that is.
Excelchat Expert 13/08/2018 - 03:48
I did not understand the last part
Excelchat Expert 13/08/2018 - 03:48
Do you want me to add month and week to the table?
User 13/08/2018 - 03:49
Yes, that is one part- I would like to be able to look at sums per calendar week and calendar month in the diagram.
User 13/08/2018 - 03:50
It is now by day, if there were a radio button to switch to week and month, that would be the very last thing to make it perfect.
Excelchat Expert 13/08/2018 - 03:52
I can have three different charts
Excelchat Expert 13/08/2018 - 03:52
for day, month and week
Excelchat Expert 13/08/2018 - 03:52
Radio button is not possible
User 13/08/2018 - 03:52
that would also do the trick- if that is possible
User 13/08/2018 - 03:57
are you still there sir/madam?
Excelchat Expert 13/08/2018 - 03:58
yes
User 13/08/2018 - 03:58
Ok
Excelchat Expert 13/08/2018 - 03:58
2 more minutes
User 13/08/2018 - 03:58
thank you so much
Excelchat Expert 13/08/2018 - 04:01
Here it is
[Uploaded an Excel file]
Excelchat Expert 13/08/2018 - 04:01
you are welcome
User 13/08/2018 - 04:01
perfect, thank you so much!
Excelchat Expert 13/08/2018 - 04:02
My pleasure
User 13/08/2018 - 04:02
this is all I needed!
Excelchat Expert 13/08/2018 - 04:02
Please provide feedback at the end of the session
User 13/08/2018 - 04:02
I will, this is a great service, thank 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.

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