# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  Google Sheets: Excel count unique by date

## Alex J Turo

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.
```

----------


## sandy666

Try PivotTable with DataModel then select CountUnique

----------


## AliGW

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.

----------


## Alex J Turo

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))

----------


## AliGW

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.

----------


## Alex J Turo

Workbook is attached. "excel forum.xlsx" Thanks for your help.

----------


## kersplash

No attachment.

----------


## Alex J Turo

Attachment

----------


## sandy666

continue from post#2

is that what you want?

----------


## AliGW

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.

----------


## Alex J Turo

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?

----------


## AliGW

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.

----------


## sandy666

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

----------


## Alex J Turo

> 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.



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.

----------


## AliGW

Have you looked at it working on your data? I attached it to my last post.  :Smilie:

----------


## Alex J Turo

AliGW: Needed to add =ARRAY_CONSTRAIN(ARRAYFORMULA(...

How would I add? =ArrayFormula(IF(NOT(ISBLANK(A2:A)),...,""))

----------


## sandy666

@post#16
this is google sheet not excel

----------


## Alex J Turo

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)

----------


## sandy666

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

----------


## AliGW

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.

----------

