Counting highest type value for a given name

**excelnoob1234567** · 10-25-2023, 07:45 AM

This sheet records e-mail reach outs to people. The response type indicates what the e-mail is responding to.
Response type KEY: 1=no response, 2=neutral response, 3=Warm response

I already have a formula that counts the number of unique names of each response type, the issue is a name can respond neutrally (2) and then warm(3) at a later date and they are then double counted.

I need to count number of unique names for each response type but only count each names highest response value. I.e. If a name responds 1 but then later responds 2 they are are counted once under 2 and not 1.

Essentially, the formula needs to check all response values for a given name, identify the highest value and add 1 to the count for that response value.

Date cant be used to identify highest value as once 3 is achieved, future e-mails are recorded as 2.

I have colour coded the desired result table so its easier to see what I'm trying to count.

File attached.

TIA.

**leelnich** · 10-25-2023, 08:29 AM

Hi, excelnoob1234567, welcome to the forum.
If a helper column is permissible, in D2, copied down:

Formula:

Please Login or Register  to view this content.

...and in F3, copied across:

Formula:

Please Login or Register  to view this content.

**excelnoob1234567** · 10-25-2023, 08:36 AM

Amazing, that works perfectly. Thank you so much for such a fast response!

**Fluff13** · 10-25-2023, 08:39 AM

Another option

Formula:

Please Login or Register  to view this content.

**leelnich** · 10-25-2023, 08:51 AM

@Fluff13- Nice! I don't have O365, but I knew somebody would chime in with a more modern response. Can you use full-column references?

**Fluff13** · 10-25-2023, 09:00 AM

Best not to use entire columns, whilst the filter function is fast it will still slow down noticeably with an entire column

**excelnoob1234567** · 10-25-2023, 09:01 AM

Thank you Fluff13! This also works beautifully. The helper row from Leelnich is actually quite usefull for filtering names by their highest response value.

This forum is great, really did not expect so many fast responses with multiple unique and viable methods. Thank you both for taking the time

**Fluff13** · 10-25-2023, 09:09 AM

Glad to help & thanks for the feedback.

**leelnich** · 10-25-2023, 09:13 AM

@excelnoob1234567 - You're welcome. Note that I just edited the formula to use full-column references, which are more robust.

If you're happy with one of these solutions, please go to Thread Tools (just above post#1) and mark your thread as SOLVED.
And since you're new to the forum...

At your discretion, you can acknowledge those who contribute solutions by clicking the *Add Reputation star below helpful posts.
You'd be surprised at how much we appreciate the gesture. - Lee

**excelnoob1234567** · 10-31-2023, 05:12 AM

Hi Leelnich,

I gave you and fluff +1 Rep, thank you again for the help.

I was wondering if the helper column could be modified so that it puts the highest number achieved on the most recent line of that name, or on the line where the highest number occurs. Currently it puts the number in line with the first occurrence of the name.

No worries if this is impossible or a pain to do, just though its worth an ask.

All the best

**AliGW** · 10-31-2023, 05:18 AM

Is this what you mean?

=IF(MAXIFS($A$2:$A$14,$B$2:$B$14,B2)=A2,MAX((COUNTIFS(B:B,B2,C:C,{1,2,3})>0)*{1,2,3}),"")

**leelnich** · 10-31-2023, 09:56 AM

Hi, Ali. Actually, I think MAXIFS would work for both parts of the formula:

Formula:

Please Login or Register  to view this content.

...or using full-column references:

Formula:

Please Login or Register  to view this content.

Regarding full-column references: yes, they slow down calculation if used in a lot of cells, but they don't break when a list is pared by deleting old rows, and they're easier to read. Perhaps the choice should be based on the actual formula, the size of the list and the manner in which it's maintained.

Of course, if you can live with structured references, the best option is to convert the list range to a Table, which resizes automatically.

Counting highest type value for a given name

LinkBack

Thread Tools

Rate This Thread

Display

Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Re: Counting highest type value for a given name

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Return the type by highest value for data that has multiple IDs, types and values

Pulling Top 5 Highest & Lowest Values of Certain Type

Help Counting Number of Times a Column is highest value

Counting with highest / Lowest Scores

[SOLVED] Counting only the cells with the highest numeric value

[SOLVED] Counting cells excluding the highest 2 outcomes

Counting the Highest value within cell with mulitple values

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions