Hi,
I am searching for a formula that will enable me to count unique values on three rows(for example I have names in three columns that may repeat themselves (tech1,tech2,tech3)), but based on two other values. (ex: week# in one column and in another I have day or night work.) Can anybody help me out?
Thanks
Hi,
The usual technique is to use a helper column which concatenates the values in all three columns and then use a COUNTIF() function in another column which looks at the helper column.
Regards
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hello,
Unfortunetly I do not see how I will be able to get the unique values of my three columns if I concatenate them and the countif does not do that either. There is one formula that was on your forum and was close to what I needed : Count Unique Values with Multiple Criteria and the formula was the following :
=SUM(IF(FREQUENCY(IF(A$2:A$21=K4,MATCH(B$2:B$21,B$2:B$21,0)),ROW(B$2:B$21)-ROW(B$2)+1),1)) with array. the only problem with that is that the data is in one column.
Hi
Would you like to attach a sample workbook??
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Here it is! I kept only the columns needed. So basically I need the unique count value of columns E,F and G depending on the value of column B and I.
sample.xlsx
Hi,
Now that you've attached the workbook can you tell us what results you expect and importantly why and how you are calculating them. Add your calculated results manually in say column K and explain your workings.
Regards
Hello,
Sorry I should have been more specific. The results I am looking for are in column H (done manually except for H3). Ideally this information I would be able to get through a Pivot table or if needs be I will enter the formula manually so it shows up in my pivots. I need the unique count of tech names(column e,f and g) for each week(column b) and for day or night work(column i). So I would need two results per week for days and nights. I hope I am not confusing anyone...
sample.xlsx
Hi,
I'm sorry but this is still confusing. I only mentioned column K as an example. I realise you want your results in column H but there are only three formulae, two of which are Divide by zero errors. With the H3 formula evaluating to 4 I don't understand (apart from the formula itself) how it is providing you with the result you expect which is related in some way to unique counts.
Please add all your manually calculated results and explain how you arrive at them, and then we should be able to work out a formula (or Pivot table) to achieve this.
Hi, sorry my attachement did not update with the changes I had made to them.. here it is.sample.xlsx
Hi,
I don't see any difference between this file and the last one you uploaded!
Hello again! Sorry for the long delay (vacation time + holidays :o) ) ok so I hope it works this time here is my sample workbook. Basically I am trying to get a formula (and hopefully input it in a pivot table) that will count the unique names (column E,F and G) depending on the week# (column B) and depending on if it was night work or day work (column I). Results needed are manually entered in column H.
Please explain how you work out the values in column H.
I manually counted the unique number of times a name (within the 3 columns (e,f & g)appears within the same week(b) and within the same time of day (column i).
If I define a name for the week (ex: call it week 42($E$5:$G$12):J3 ) I can get the count of unique names for that week but then again it dosen't work if there is blanks in there (it works with no blanks called weeks42($E$5:$F$12): appearing in J4 ).
I can't work out the formula to add another condition in order to add if it's day or night. I added the attachement with the formulas in column J (sum(1/countif(week42,week42)) formula in array.
Hi,
Does the attached help.
A VBA solution would also be another option.
Hi! That is great! it is perfect! I'm just not getting why the formula is not working on two lines (L3 (should get 2 and L9 should get 1)?
Sorry, my mistake. After I completed the formulae I added the text 'Helper Columns' in L2 to, well help you!
Unfortunately L3 was then compromised by this. Just delete the L2 text. In fact the If(AND(L2.. bit in L3 is only needed because it's the first row and the cell above will always be empty - or should be.
L9 should not have a 1 since the N9 '42DaySJ' already appears above it in N7. i.e. SJ has already been counted once for this particular week number and day/night value.
Regards
Last edited by Richard Buttrey; 01-11-2012 at 10:45 AM.
Ah yes! perfect! thank you so very much!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks