Hello All!
I have been completely stumped by this challenge: I have a two column table: col A is a first name, col B is a date. The table is formatted as an excel table. Per the rules of this challenge I am unable to do any of the following:
- I can't change any of the data IN the table itself
- I HAVE to use a formula (no pivot tables, power pivot, VBA, etc.)
- I can't use the COUNTIF/COUNTIFS formulas
What I need to do is find how many instances of the combination of CONCATENATE(ColumnA, MONTH(ColumnB)) exist in the table. I added a col C to concatenate col A and the month of Col B so that I would have a static reference to bump against the two table columns. I also added a col D to perform the count for each concatenation. The output that I expect is:
Name DOB Concat Count Richard 7/1/88 Richard7 2 Patty 2/18/58 Patty2 1 Paul 9/6/56 Paul9 1 Paul 10/01/67 Paul10 1 Raymond 4/23/50 Raymond4 1 Richard 3/21/44 Richard3 1 Richard 7/9/65 Richard7 2
As you can see "Richard7" has a count of 2 on line 1 and line 7 because each time it searches for "Richard7" it finds itself, and then another occurence of itself. The remaining lines have a count of 1 because each search only finds itself.
I have tried a lot of different angles with SUM/IF/MATCH, DCOUNTA, SUMIFS, etc. and I just can't get this to work. The ONLY formula that I have managed to get to work is =COUNTIF($C$3:$C$77,Table1[@Name]&MONTH(Table1[@DOB])), but of course I can't use this as it breaks rule #3. I have attached a sample of the table for reference. Any help would be IMMENSELY appreciated.
Bookmarks