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. :)
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi and welcome to MrExcel.

Does this help?.....

Excel Workbook
ABCDEFGHIJ
1AccountExecutiveAccount AttributeCityMeeting DateCityAttribute CountIB100
2AA1JohnTokyoFeb-12Beijing
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
 

nikitsg

New Member
Joined
Mar 1, 2013
Messages
5
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

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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

New Member
Joined
Mar 1, 2013
Messages
5
Brilliant.. It works!!! Thanks ...
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

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

Ak
 

nikitsg

New Member
Joined
Mar 1, 2013
Messages
5
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

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi Nikit.

I hope that you get the solution you require.

Good luck.

Ak
 

Forum statistics

Threads
1,078,500
Messages
5,340,746
Members
399,393
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top