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

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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-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
 

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

ADVERTISEMENT

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

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,411
Messages
5,528,617
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top