Hi,
Please see attached workbook with sample data and expected results.
I require a formula which produces the following:
Unique count and Output total serial number(s) for corresponding ID WHERE Status = Issued, Renewed or Repair.
Hi,
Please see attached workbook with sample data and expected results.
I require a formula which produces the following:
Unique count and Output total serial number(s) for corresponding ID WHERE Status = Issued, Renewed or Repair.
Hi makaveeti,
Try this formula in F2 and pull it down.
Formula:Please Login or Register to view this content.
If this formula doesn't give the same answer you want, look for spaces in some of your data. That would cause them not to match. IE 5space is not equal to 5nospace
Note: Column B is blank so you can't sort and have column A stay with columns C and D.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
If you're only wanting to count where the status is ISSUED, RENEWED, or REPAIR, try this:
=SUM(COUNTIFS($A$2:$A$26,$A2,$C$2:$C$26,{"ISSUED","RENEWED","REPAIR"},$D$2:$D$26,$D2))
In your expected results, you have 3 rows with the answer of 3 for each, but the S/N's don't match for those - is that an error on your part? There are a couple of others that don't match so maybe I'm not understanding your requirement exactly - I'm assuming the ID AND the S/N's need to match.
Hi Greg,
That's not an error. I'm sorry if my explanation of the logic was poor.
Rows 9 10 and 11 contain an ID which is 578954125102.
There are 3 unique serial numbers associated to this ID which have a status of ISSUED:
8328014380747500
9192018767647000
9192018767627000
Therefore the unique count of serial numbers for that ID = 3
There is one thing I forgot to mention and that is on my wider dataset, Column A contains some values which are alphanumeric
Maybe the logic should be to look at a unique ID and do a unique count of serial numbers for that unique ID where the status is ISSUED, RENEWED or REPAIR.
Hope that makes sense.
Last edited by makaveeti; 04-05-2022 at 04:54 AM.
How aboutFormula:Please Login or Register to view this content.
Hi Fluff13
That worked for the sample dataset however on my larger dataset, where I would expect to see a 0, I am seeing an error in the calculation for users who do not have any serial numbers that don't have a status of issued, renewed or repaired. My bad.
See row 27 on the attached updated sample workbook.
On a side note I found a formula that I didn't know I had (see Sheet2). It does the same job however it looks up the IDs in sheet1 from sheet2 and then returns the result. Can you adapt that formula so the lookup is removed and place it in Sheet1 Column G?
Thanks in advance.
To get rid of the error, you can useFormula:Please Login or Register to view this content.
Thanks Fluff13 - That has resolved the issue.
I'll mark the thread as solved.
Any chance you could look at the alternative formula in sheet2 of the workbook?
The frequency function is not something I really know about, but this seems to give the same resultFormula:Please Login or Register to view this content.
Yes that appears to give the same results. Thanks for your time with it. I might try and dig out who provided it to me originally to verify that it's being used correctly.
As for the original question - All solved. Thanks.
Glad to help & thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks