Excel automation / formula required (guess, if + vlookup + countif)

nikitsg

New Member
Joined
Mar 1, 2013
Messages
5
I've been struggling to get a solution to this business related problem for a couple of days now.. Can someone please help with finding the right approach / formula to get the desired solution?

Two tables:

Table 1.. (where the results are to be displayed in Attribute Count column) Attribute Count column currently has the "desired result", manually derived.

CityAttribute Count
Beijing0
New York2
Tokyo1
Seoul0
Paris0
Mumbai0
London1

<tbody>
</tbody>

Table 2.. (Data Source)

AccountExecutiveAccount AttributeCityMeeting Date
AA1JohnTokyoFeb-12
AA2MikeIB100LondonMar-12
AA2TomIB100London
AA2HarryIB100LondonAug-12
AA3ShaunIB100Mumbai
AA4GregBeijingJun-12
AA5SandyIB100New YorkNov-12
AA5FrankIB100New YorkNov-12
AA6TerryIB100New YorkJan-12
AA6TonyIB100New York
AA7MathewIB100TokyoFeb-12

<tbody>
</tbody>


I want to write a formula that helps me get the "Attribute Count" column automatically populated with the count. The count should represent:
How many "unique" accounts with 'IB100' attribute have had a meeting with us in each city?
For example: For Tokyo, the data source says a meeting happened with Mathew of AA7 and Company Attribute is IB100. Also, a meeting also happened with John of AA1 based in Tokyo, but its Company Attribute is null / nothing. So, the count in Table 1 is shown as 1.

Hope I have been able to explain the query. :)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi and welcome to MrExcel.

Does this help?.....

Excel Workbook
ABCDEFGHIJ
1AccountExecutiveAccount AttributeCityMeeting DateCityAttribute CountIB100
2AA1JohnTokyoFeb-12Beijing0
3AA2MikeIB100LondonMar-12New York2
4AA2TomIB100LondonTokyo1
5AA2HarryIB100LondonAug-12Seoul0
6AA3ShaunIB100MumbaiParis0
7AA4GregBeijingJun-12Mumbai1
8AA5SandyIB100New YorkNov-12London1
9AA5FrankIB100New YorkNov-12
10AA6TerryIB100New YorkJan-12
11AA6TonyIB100New York
12AA7MathewIB100TokyoFeb-12
13
Sheet2


The formula in H2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
You can copy/cut and paste the results table to another sheet and it will automatically add the relevant sheet name.
You will need to change the cell references to suit your actual layout.

The above formula was taken from here.....
Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Excel resource

*Have I made an error with Mumbai, or did you in your example results?*

I hope that helps.

Good luck.

Ak
 
Upvote 0
Hi AK.. While I think this is brilliant, but we missed a point. We need to take the 'Meeting' into consideration as well i.e. the city / the attribute needs to be counted only if there is a meeting date. That is the reason behing the 'Mumbai' related error. Although the account based in Mumbai is 'IB100' but there is no meeting with that account. So, we need to ignore 'Mumbai' count. So, its zero in my sample sheet. Can you please help with this?

Thanks!
NG
 
Upvote 0
Hi NG,

I think that you missed out the date criteria! :)

Try this......

=SUM(IF((G2=$D$2:$D$12)*($I$1=$C$2:$C$12)*($E$2:$E$12<>""), 1/COUNTIFS($D$2:$D$12, G2, $A$2:$A$12, $A$2:$A$12, $C$2:$C$12, $I$1,$E$2:$E$12,"<>")), 0)

Ak
 
Upvote 0
Hi AK,
There's another tweak that I need to consider for another column similar to column C. Can you help?

Lets say I have two values in Column C - IB100, and IB200. This attribute varies for different accounts. The final answer should take into consideration both, IB100 and IB200 attributes. Currently I had proposed that we need to look for IB100 only. Can this change be accommodated in this formula? I tried making some changes, but unable to get through.

Example (if the above is confusing):
Column C can consist IB100, IB200, IB300, Blanks. We need the final count (keeping all other criteria as is) only for accounts with attribute IB100 and IB200 and skipping the accounts with IB300 / Blanks attribute.

Thanks in advance! :)

Nikit
 
Upvote 0
Hi Nikit,

I'm sorry but I am unable to work out how to add the extra OR criteria for IB200.
If no one else jumps in with a solution, then I suggest that you post a new question, explain what you already have and refer to this post.

I'm sorry that I couldn't give you the answer you require.

Good luck.

Ak
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top