Question description:
This user has given permission to use the problem statement for this
blog.
I have two lists of employers followed by 4 numbers. I then have another list with the same thing. The problem is that one has employers that the other doesn't so it doesn't line up to just sum each across. Is there a way I can find the sums without having to manually count?
Solved by A. A. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
22/06/2018 - 12:28
marketing research analyst
Excelchat Expert
22/06/2018 - 12:28
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern.
User
22/06/2018 - 12:28
thank you!
User
22/06/2018 - 12:29
can I copy and paste into that excel sheet?
Excelchat Expert
22/06/2018 - 12:29
I understand that you need help with your formula.
Excelchat Expert
22/06/2018 - 12:29
Yes sure, or you can also forward the excel data here
Excelchat Expert
22/06/2018 - 12:30
If you click the paper clip icon, you can attach and forward the data you're working on.
Excelchat Expert
22/06/2018 - 12:35
Hello are you still there?
Excelchat Expert
22/06/2018 - 12:38
To answer your question, yes there is a way to find the sums without manually counting the employers. First you have to merge/combine the 2 lists, then you can count the # of employers using a formula without counting duplicates. To do that, add a new column after the employer's name, name that new column as (COUNT OF UNIQUE EMPLOYERS), then on 1st row, add this formula:
Excelchat Expert
22/06/2018 - 12:40
=1/COUNTIF(A:A,A2)
Excelchat Expert
22/06/2018 - 12:41
Given that the list of employers is in column A
Excelchat Expert
22/06/2018 - 12:42
Then you can sum column B using formula =SUM(B:B) anywhere on a free cell, then the number returned will be the total count of unique employers on your list
Excelchat Expert
22/06/2018 - 12:46
Here's a sample for your reference. You can paste your employer list on column A and the formula should work the same, just make sure it is copied to the last row of your data.
[Uploaded an Excel file]
Excelchat Expert
22/06/2018 - 12:46
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon.. I sincerely hope we were able to address your concern.
Excelchat Expert
22/06/2018 - 12:47
Thanks for using Got It Pro-Excel. Please give your kind feedback for our service. Have a good day! :)
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.