Hi,
I need some help, I've spent an hour browsing a solution and I keep failing at putting examples together!
Please can someone provide a formula to solve this:
- I need to calculate unique values in Column A
- I need two criteria, if these two match then it will count unique values in Column A
Criteria 1: Check Column F matches criteria on Cell A2
Criteria 2: Check Column G does not contain criteria on Cell A3
Thanks in advance
There are instructions at the top of the page explaining how to attach your sample workbook.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Here it is. Thank you
There is no manually calculated result - what result are you expecting from your sample data? A value, not a description, please.
I want the number of unique names (sheet 2, column A) based on the criteria I set in sheet 1.
And that number is? Value, please.
Are you still using Excel 2013?
they will be names. so I just want to count it, just once, even though there may be duplicate names in the column. is that what you mean?
No, I wanted you to tell me what you were expecting the actual result to be for your very limited sample data. I assume 1, but you have failed to confirm this. Repeating a description when I have said I want a value and not a description doesn't get us very far!
Try this:
=SUMPRODUCT((1/(COUNTIF($A$2:$A$8,$A$2:$A$8))*($B$2:$B$8=Sheet1!$A$2)*($C$2:$C$8<>Sheet1!$A$3)))
Are you still using Excel 2013?
A COUNT cannot be a name - it has to be a number!!!they will be names
Last edited by AliGW; 01-11-2022 at 06:31 AM.
Oh, I'm so sorry! Totally went over my headYes - the value I would expect is '1'
That works perfectly, thank you so much!
Lastly, do you have a solution if I wanted to extend data beyond the current data set (currently A2-A8, but if I set it at A2-A100 [for example]) as I'll be continuously adding data and currently it returns a #DIV/0 error if I set it to anything beyond A8. I suppose it's worth checking to omit empty cells?
Yes, I do - use a structurded table, and then you won't need to worry about the range needing to grow:
=SUMPRODUCT((1/(COUNTIF(Table1[Name],Table1[Name]))*(Table1[Column]=Sheet1!$A$2)*(Table1[Column2]<>Sheet1!$A$3)))
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Worksheet name : Sheet1
Cell C2 array formula
Formula:
Please Login or Register to view this content.
AliGw's Cell D2 formula
Formula:Please Login or Register to view this content.
Thank you both, very helpful and saved me time
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks