Count Formula??

[lp14301]

I am trying to write a formula to count the number of customers that are in a given year and for a specific collector. The account numbers are in column "A", the Date is in column "B", and the collector id is in column "C". I need a count of customer numbers for collector 1 that are in years 2015 and 2016. Any ideas?

Customer # Date Collector
1234 2015 1
5678 2016 1
9234 2015 4
7165 2017 7

[TMS]

Pivot Table.

[lp14301]

Pivot Table.

I can't just use a pivot. I need to automatically update another report with these figures every time the report gets downloaded into the spreadsheet. I really need a formula to calculate it.

[TMS]

Please see the yellow banner at the top of the page. Show your expected results.

[lp14301]

I am trying to write a formula to count the number of customers that are in a given year and for a specific collector. The account numbers are in column "A", the Date is in column "B", and the collector id is in column "C". I need a count of customer numbers for collector 1 that are in years 2015 and 2016. Any ideas?

Customer # Date Collector
1234 2015 1
5678 2016 1
9234 2015 4
7165 2017 7

Expected results would be 2 customers 1234 and 5678 are both collector 1 and in years 2015 and 2016.

[TMS]

Then:

Please Login or Register  to view this content.

[lp14301]

Then:

Please Login or Register  to view this content.

Ok i didn't give enough example data. That formula only pulls back the number of lines where the year and collector meet the requirement. There may be multiple lines with the same customer number so I need the formula to only count the customer number once if it is in there multiple times for those years. Any thoughts?

[lp14301]

Ok i didn't give enough example data. That formula only pulls back the number of lines where the year and collector meet the requirement. There may be multiple lines with the same customer number so I need the formula to only count the customer number once if it is in there multiple times for those years. Any thoughts?

For example. there are three instances that would fall into the formula above for collector 1 in years 2015 and 2016, however there are two instances where account 1234 would be counted and I only need it to count one time. No duplicate account numbers. The formula result should still be 2 not 3.

Customer # Date Collector ID
1234 2015 1
5678 2016 1
9234 2015 4
7165 2017 7
1234 2015 6
1234 2016 1

[TMS]

Ok i didn't give enough example data.

OK, last chance ...

Please see the yellow banner at the top of the page. Show your expected results.

[lp14301]

OK, last chance ...

I did. the formula result should be 2 not 3. See the last line in my previous post. I am new to this forum and excel. What do you need from me? I need help.

[Pete_UK]

Read the yellow banner at the top of the screen - it tells you how you can attach a sample Excel workbook to one of your posts. If you do this then we can see exactly how your data is laid out, i.e. which columns you use for your data and where you want to see the results. In your sample file you should show the expected results (manually) and explain clearly how you have arrived at them.

Also, you should edit your profile (using User CP) to show the version of Excel that you are using, as the solutions proposed may be dependent on this. "16" means nothing to me.

Hope this helps.

Pete

[TMS]

I'm guessing you have some data in a worksheet, in a workbook? I want that, or something very similar to it, that preserves your customers's and collectors' privacy ... in an .xlsx workbook. Sufficient examples of what your data looks like, in the right columns. And I want to see the answer(s) you expect in the cell(s) where you want the result(s).

That way, I don't have to copy your data into a blank spreadsheet and use Text to Columns to separate it out (like I did the first time) so that I can prepare and test a solution.

Yellow Banner at the top of the screen
HOW TO ATTACH YOUR SAMPLE WORKBOOK:
lp14301 Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.