If/And/Count Formula

[JRose0303]

I hope I articulate these clear enough:

1. If the Provider and the Address equals the Provider and the Address associated with it, then input the Company that correlates with it (I typically use VLOOKUP but I don't know how to manipulate it to look up two values)
2. If the Provider and the Address equals the Provider and the Address associated with it, then count the number of times the Provider and the Address appear

PROVIDER	ADDRESS	ORGANIZATION
Bob Newhart	123 ABC St	Company 1
The Flash	456 Pickup Sticks Rd	Company 2
Spiderman	789 Holiday Lane	Company 3
The Flash	456 Pickup Sticks Rd	Company 2
The Flash	456 Pickup Sticks Rd	Company 2
Bob Newhart	123 ABC St	Company 1

Thank you for your help with this!

[JohnTopley]

See attached example.

[JRose0303]

Ok, for the count formula, I'm trying to manipulate this into my report; however, I have thousands of providers and addresses that I'm trying to count the occurrences of and it's just not working out. For some reason I'm wanting it to be as simple as if Column A and Column B equal Column A and Column B, then count the occurrences that Column A matches Column B for each of those providers.

[Destroy]

Your description of what you are looking for makes no sense. "if Column A and Column B equal Column A and Column B"
You need to explain better what you are trying to get at.

[JohnTopley]

For VLOOKUP, you must a list of supplier details (similar to the table in your first post). If this correct, then using the SUMPRODUCT formula provides the results required.

Can you post a sample workbook showing the input data and the output expected?

Click "go advanced" and then "Manage attachments"

[JRose0303]

Ok, I'm not very good at explaining things (sorry). Attached is an example. I'm basically taking the raw data and condensing it. Referral Report Help.xlsx

[JohnTopley]

Attached shows one possible solution: the "helper" column can be placed anywhere so just change the range as required.

Hope this helps.

[JohnTopley]

I found another formula on this forum which does the required calculation!

[JRose0303]

This is AMAZING! It works beautifully! Thank you so much for your help! A report that usually takes days and a lot of crossed eyes and coffee will only take hours. I cannot thank you enough!

[JRose0303]

I think I marked this solved too soon I have three reports/locations that once they're added up based on the individual location, I combine the locations and get overall totals. So I'll take the data from the individual locations reports, and sum the number of occurrences overall. So Bob Newhart at 123 ABC St could have 3 separate totals (from the individual reports) that I want to sum. I'm fairly positive it's not as simple as replacing COUNTIF with SUMIF.

[JohnTopley]

I don't if you can calculate over 3 sheets where ( I believe) the list of suppliers and addresses vary i.e a supplier/address might only exist in one report.

Is it practical to merge the data into one report and then use the formula(s) already supplied?

If not hopefully one the Excel gurus can provide an answer.

It would be possible using VBA.