LinkBack URL
About LinkBacks
How do I count only unique tickets from DATA column A?
What would the formula need to be for SUMMARY cell B3?
Please Login or Register to view this content.Please Login or Register to view this content.
Google Sheets: Excel count unique by date
How do I count only unique tickets from DATA column A?
What would the formula need to be for SUMMARY cell B3?
Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by AliGW; 04-01-2018 at 04:52 PM.
Try PivotTable with DataModel then select CountUnique
If you don't want to use a pivot table, these formulae will do what you want:
Unique Values =SUM(IF(FREQUENCY(IF($B$2:$B$7=D2,$A$2:$A$7),$A$2:$A$7)>0,1))
All Cases =COUNTIFS($A$2:$A$7,"<>",$B$2:$B$7,D2)
where D2 contains the date you are testing for.
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.
Re: Excel count unique by date
AliGW: Thank you, the formula is what I was hoping for. I added to my SS but the counts are 0. What could be wrong?
=SUM(IF(FREQUENCY(IF('Ticket Tracker'!$G$2:$G$1000=A3,'Ticket Tracker'!$A$2:$A$1000),'Ticket Tracker'!$A$2:$A$1000)>0,1))
It works fine here with your sample data. Attach your workbook here and I will have a look.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Workbook is attached. "excel forum.xlsx" Thanks for your help.
No attachment.
Attachment
Re: Excel count unique by date
continue from post#2
is that what you want?
Your sample data contained numbers, which is what I devised the solution for, but the real data contains text. Try this instead:
=SUM(IF(A3='Ticket Tracker'!$G$2:$G$1000,1/(COUNTIFS('Ticket Tracker'!$G$2:$G$1000,A3,'Ticket Tracker'!$A$2:$A$1000,'Ticket Tracker'!$A$2:$A$1000)),0))
... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
AliGW: I added the revised formula and it didn't seem to work.
Sandy666: Those are the numbers I am expecting to see what how are you doing this? What's the formula?
Well, it works on your latest sample data. What else haven't you told us? By the way, "didn't seem to work" isn't really very helpful: in what way did it not work? As we have no expected outcomes, it's hard to know exactly what you are expecting.
Last edited by AliGW; 03-26-2018 at 10:28 AM.
select whole data in your source (column A & B - data only)
insert - PivotTable
then...
cpt.jpg
then add Date to Rows area
add ZD Ticket to Values area twice then for the first select
dc.jpg
for the second simply Count
edit:
If you doesn't see checkbox DataModel you will need to install PowerPivot add-in from MS site
and make your profile easier to read: 2010 not 10 or another real version
Last edited by sandy666; 03-26-2018 at 10:52 AM. Reason: see edit
The workbook is exact minus any sensitive data. The columns for the needed formula are untouched other than the link path to read test dot com.Originally Posted by AliGW
Have you looked at it working on your data? I attached it to my last post.
AliGW: Needed to add =ARRAY_CONSTRAIN(ARRAYFORMULA(...
How would I add? =ArrayFormula(IF(NOT(ISBLANK(A2:A)),...,""))
@post#16
this is google sheet not excel
Yes, converted it to gSheet once I got it working. Now I just need to do the ArrayFormula.
=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(A4='Ticket Tracker'!$I$2:$I$1000,1/(COUNTIFS('Ticket Tracker'!$I$2:$I$1000,A4,'Ticket Tracker'!$A$2:$A$1000,'Ticket Tracker'!$A$2:$A$1000)),0))), 1, 1)
so try here with new thread:
For Other Platforms(Mac, Google Docs, Mobile OS etc)
and update your profile about excel version: 10 to me is windows 10 not an any excel version
Please do NOT start a new thread - this one has been moved.
It would have helped from the outset if you had mentioned that you wanted this for Google Sheets: my solutions were for Excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks