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

[nikitsg]

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.

[TABLE="width: 172"]
<tbody>[TR]
[TD]City[/TD]
[TD]Attribute Count[/TD]
[/TR]
[TR]
[TD]Beijing[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Tokyo[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Seoul[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Paris[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mumbai[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Table 2.. (Data Source)

[TABLE="width: 364"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Executive[/TD]
[TD]Account Attribute[/TD]
[TD]City[/TD]
[TD]Meeting Date[/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]Tokyo[/TD]
[TD="align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]Mike[/TD]
[TD]IB100[/TD]
[TD]London[/TD]
[TD="align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]Tom[/TD]
[TD]IB100[/TD]
[TD]London[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]Harry[/TD]
[TD]IB100[/TD]
[TD]London[/TD]
[TD="align: right"]Aug-12[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]Shaun[/TD]
[TD]IB100[/TD]
[TD]Mumbai[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA4[/TD]
[TD]Greg[/TD]
[TD][/TD]
[TD]Beijing[/TD]
[TD="align: right"]Jun-12[/TD]
[/TR]
[TR]
[TD]AA5[/TD]
[TD]Sandy[/TD]
[TD]IB100[/TD]
[TD]New York[/TD]
[TD="align: right"]Nov-12[/TD]
[/TR]
[TR]
[TD]AA5[/TD]
[TD]Frank[/TD]
[TD]IB100[/TD]
[TD]New York[/TD]
[TD="align: right"]Nov-12[/TD]
[/TR]
[TR]
[TD]AA6[/TD]
[TD]Terry[/TD]
[TD]IB100[/TD]
[TD]New York[/TD]
[TD="align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]AA6[/TD]
[TD]Tony[/TD]
[TD]IB100[/TD]
[TD]New York[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA7[/TD]
[TD]Mathew[/TD]
[TD]IB100[/TD]
[TD]Tokyo[/TD]
[TD="align: right"]Feb-12[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Akashwani]

Hi and welcome to MrExcel.

Does this help?.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Account	Executive	Account Attribute	City	Meeting Date		City	Attribute Count	IB100
2	AA1	John		Tokyo	Feb-12		Beijing	0
3	AA2	Mike	IB100	London	Mar-12		New York	2
4	AA2	Tom	IB100	London			Tokyo	1
5	AA2	Harry	IB100	London	Aug-12		Seoul	0
6	AA3	Shaun	IB100	Mumbai			Paris	0
7	AA4	Greg		Beijing	Jun-12		Mumbai	1
8	AA5	Sandy	IB100	New York	Nov-12		London	1
9	AA5	Frank	IB100	New York	Nov-12
10	AA6	Terry	IB100	New York	Jan-12
11	AA6	Tony	IB100	New York
12	AA7	Mathew	IB100	Tokyo	Feb-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

 

[nikitsg]

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

 

[Akashwani]

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

 

[nikitsg]

Brilliant.. It works!!! Thanks ...

 

[Akashwani]

Hi,

I'm pleased it worked for you and thanks for the feedback.

Ak

 

[nikitsg]

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

 

[Akashwani]

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

 

[nikitsg]

Thanks, AK. Appreciate you taking the time to help.

Nikit

 

[Akashwani]

Hi Nikit.

I hope that you get the solution you require.

Good luck.

Ak