Unique count of values based on corresponding ID and IF statement with similar condition

**makaveeti** · 04-14-2022, 01:07 PM

Hi all,

Sample data attached with expected results in columns highlighted in yellow. I require 4 formulas as follows (Hope I have described the logic correctly):

Formula 1 - Unique count of Active Serial Numbers belonging to an ID
Unique count of Col A (serial numbers) belonging to a unique ID in Col D where Col L = Yes

Formula 2 - Unique Count of active serial numbers that are expiring within 90 days.
Unique count of Col A (serial numbers) belonging to a unique ID in Col D where Col L = Yes and Expiry date (Col K) is within 90+NOW()

Formula 3 - Check IF serial number with latest issue date is Valid.
For each unique ID (Col D), If Col L = Yes for the serial number (Col A) with the latest issue date (Col J) return word "Valid"

Formula 4 - Check latest expiry date for serial numbers belonging to unique ID
For each unique ID (Col D), If Col C = "Issued" "Renewed" or "Repair" for the serial number (Col A), check:

If LATEST expiry date (Col K) is within 90+NOW() then return Value "90 Days" or if it is within 30+NOW() then return "30 Days"
If LATEST expiry date (Col K) is greater than 90+Now() then return value "Valid"
If LATEST expiry date (Col K) is less than NOW() then return value "Expired"

**Limor_OP** · 04-14-2022, 01:49 PM

Hi,
1. are you using Office 365?
2. what does it mean:
"Unique count of Col A (serial numbers) belonging to a unique ID in Col D where Col L = Yes" ?
if the ID in column D is not unique then the ID in A should not be included on your list?

**makaveeti** · 04-14-2022, 02:05 PM

1. Yes I’m using 365
2. Column D contains User IDs, Column A contains token serial numbers which belong to a User ID.

**TMS** · 04-14-2022, 02:23 PM

2. Column D contains User IDs, Column A contains token serial numbers which belong to a User ID.

They're not unique, as far as I can see. The first four entries are two pairs of identical records.

Formula:

Please Login or Register  to view this content.

works as expected except for the first four entries.

**Limor_OP** · 04-14-2022, 02:25 PM

Just to be sure I understand your request - please see attached in A27 and down, and advise if these are the results you are expecting for formula 1

=UNIQUE(FILTER(A2:A25,L2:L25="yes"))

**makaveeti** · 04-14-2022, 03:11 PM

TMS - You got it right. Yes the first 4 expected entries are incorrect and should read 1. I'll upload an updated workbook in post 1.

As far as the thread goes.

1. Formula 1 - TMS has answered.

Now I just need the other 3 formulas as stated in the original post.

**makaveeti** · 04-14-2022, 03:22 PM

TMS - I forgot to mention that in my real data some of the values in column A are alpha numeric. Will that require a change to the formula?

**TMS** · 04-14-2022, 03:43 PM

I forgot to mention that in my real data some of the values in column A are alpha numeric. Will that require a change to the formula?

If it works as is, no. If it doesn't, yes. Have you tried it? What part of the formula makes you think it may need to change?

**makaveeti** · 04-14-2022, 03:56 PM

Yes I just tried it. I added rows 26-29 to the sample data. Column A rows 26-27 contain alphanumeric values. The expected result is 2 but the formula is returning 0.

**Limor_OP** · 04-14-2022, 04:00 PM

The formula works.
Maybe you didnt update the ranges $A$2:$A$25, $L$2:$L$25, $D$2:$D$25
change it to 29 instead of 25 .

**makaveeti** · 04-14-2022, 04:17 PM

Apologies - School boy error.

Thank you belinda200. I updated the ranges as stated. TMS's formula handles the alphanumeric values too and is working.

**makaveeti** · 04-14-2022, 04:30 PM

TMS - I have adapted your formula to answer Formula 2.

=IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$29, ($D$2:$D$29=$D2)*($L$2:$L$29="Yes")*($K$2:$K$29<90+NOW())))),0)

I just need Formula 3 and 4 now..

**makaveeti** · 04-14-2022, 04:56 PM

Formula 3 - Check IF serial number with latest issue date is Valid.
For each unique ID (Col D), If Col L = Yes for the serial number (Col A) with the latest issue date (Col J) return word "Valid"

I have this for formula 3 so far. How can it be adapted so that it only checks column L based on the latest date in column J?

=IF(AND(COUNTIFS($D$2:$D$29,D2,$L$2:$L$29,"Yes")),"Valid","")

If a unique identifier is required to add to the formula you can use Column A

**TMS** · 04-14-2022, 06:47 PM

To avoid the schoolboy errors, these formulae determine the last row of data based on column D (note that column D has numeric content).

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

**makaveeti** · 04-14-2022, 07:41 PM

Thanks TMS. So these 3 formulas are working fine.

Formula 3 is giving me the expected results. Can I just clarify though, your post above says 'these formulae determine the last row of data based on column D' - Does your formula do a sort whereby the last row of data in column D happens to be the latest issue date (Col J)? I ask because I see no reference to Col J in the formula.. maybe there is no need to reference Col J?

When I download the report from the server, Col J is unsorted by default. So that I understand it can you explain the design of it please?

**TMS** · 04-14-2022, 07:52 PM

The formula has nothing to do with column J. It just uses a very large numeric value to check against the values in column D. It will always be bigger than any value present and will, therefore, return the last row number.

The formula just needs to refer to a column that is fully populated (no blank entries) with numeric values.

You can test this using

Formula:

Please Login or Register  to view this content.

**TMS** · 04-14-2022, 07:54 PM

Note: that is equivalent to just:

Formula:

Please Login or Register  to view this content.

**makaveeti** · 04-14-2022, 08:04 PM

Ok thanks for your reply. To be honest I'm still struggling to understand how it works and how it works by not needing to look at column J. Forgive my lack of knowledge and experience as I not a proficient as yourself with Excel.

What are your thoughts on Formula number 4?

**makaveeti** · 04-15-2022, 06:41 AM

TMS -

How can I adapt formula 1 to look at multi values for the condition when doing the count?

At present the formula is:

=IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$25, ($D$2:$D$25=$D2)*($L$2:$L$25="Yes")))),0)

I would like to adapt the above in red to check column c instead for the following values "Issued" "Renewed" or "Repair"

I tried replacing ($L$2:$L$25="Yes") with ($C$2:$C$25={"Issued","Renewed","Repair"}) but that doesn't calculate correctly.

**TMS** · 04-15-2022, 07:20 PM

See if this gives you what you are expecting:

Formula:

Please Login or Register  to view this content.

**TMS** · 04-15-2022, 07:38 PM

Self updating version:

Formula:

Please Login or Register  to view this content.

**makaveeti** · 04-15-2022, 07:54 PM

Thanks TMS - They both work perfect.

This was the formula I was using previously to do the same job however I imagine both formulas above are a much more efficient and less resource intensive way of doing the count?

=COUNT(0/FREQUENCY(ROW($1:$50),MATCH($A$2:$A$50,$A$2:$A$50,)*($C$2:$C$50={"Issued","Repair","Renewed"})*($D$2:$D$50=$D2)))-1

**TMS** · 04-15-2022, 08:08 PM

No idea. Doesn't seem slow but I have no tools to measure the speed. I only tend to worry about performance when there is a noticeable delay. Logically, you'd expect new functions to be optimised but I wouldn't stake my life on it.

Unique count of values based on corresponding ID and IF statement with similar condition

LinkBack

Thread Tools

Rate This Thread

Display

Unique count of values based on corresponding ID and IF statement with similar condition

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Re: Unique count of values based on corresponding ID and IF statement with similar conditi

Thread Information

Users Browsing this Thread

Similar Threads

Count unique values if consecutive condition

Count Unique Values in one Column based on a condition in another column-Please help!

[SOLVED] Count Unique Values in a Range with Condition

count unique values based on 1 condition

Count unique values based on condition

Re: Count unique values from different columns with a condition

Bookmarks

Bookmarks

Posting Permissions