Solved Excel Count unique value

April 21, 2015 at 07:08:57
Specs: Windows 7
Hi, I am trying to do a count in excel for how many items were reviewed.

I currently have a worksheet with the fields "STATE, ID#, REVIEWED DATE" .

In another sheet I want to count how many cells have a Reviewed date. so if there is no date i don't want it included. But i also need it to be for each state. Since these are profiles there are many state fields that are duplicates. For example:

State -- ID# -- REVIEWED DATE
PA -- 2 -- 2.18.15
PA -- 1 --
PA -- 2 -- 2.18.15
NJ -- 2 --
NJ -- 1 -- 2.18.15
NJ -- 2 --
NY -- 1 -- 2.14.15
NY -- 2 -- 2.12.15

What id like is a field in another worksheet with
STATE -- Amount Reviewed
PA -- 2
NJ -- 1
NY -- 2

I hope im making much sense, any help would be greatly appreciated!


See More: Excel Count unique value

Report •


#1
April 21, 2015 at 14:57:11
✔ Best Answer
3 Assumptions:

1 - Your data is in A1:C9
2 - You have a list of unique states in Column F.
3 - You are using Excel 2007 or later (COUNTIFS first became available in Excel 2007)

Put the following formula G2 and drag it down

         F               G
1      State           Count
2       PA            =COUNTIFS($A$2:$A$9,F2,$C$2:$C$9,"<>"&"")
3       NJ
4       NY

The second criteria looks weird, but that's how you COUNTIF non-blank cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 22, 2015 at 13:39:32
WORKED PERFECTLY THANK YOU!!

Can you help with one more thing?

How do i do a countifs for 3 criterias? for example,

F G H
1 PA Phila 3/15/2015
2 PA Delaware 3/15/2015
3 PA Delaware 2/15/2015


For example, i want a calculation of how many dates i have for Delaware PA and Phila.
so for the # of dates i have for PA Delaware would equal 2 and for PA Phila would equal 1


Report •

#3
April 22, 2015 at 13:46:08
disregard, i dunno why it wasnt working for me yesterday but i tried it again today by adding the third criteria and using "<>"&""). ur the man bro thanks !

Report •

Related Solutions


Ask Question