YTLE#56: Excel Count Unique Values Greater Than $30

Dear Most Amazing Excelrs In The World,

I am trying to count the unique values that are greater than 30 in the range A1:A5. The values a

30
45
45
90
1

The answer should be 2 because 45 and 90 would be counted.

I have successfully created a few solutions with a Pivot Table and Advanced Fliter, but when I try to do one Array formula in one cell, I have no luck.

Any ideas about how to count the unique values that are greater than 30 in the range A1:A5 with an array formula?

Hi, I am wondering if it is possible to count the number of unique values in two columns greater than some other value. To clarify, if I have two columns (A and B) with the values listed below.

A B

3 6
2 3
4 2
6 8
5 10

I want to count how many unique values between the two columns are less than 5. In this case, there should be 3 (2,3 and 4 from column A as well as 2 and 3 from B fulfill this condition). Is there any way to do this efficiently without using VBA? I have searched this forum and a few others and have seen SUM(IF(FREQUENCY(...)) being used in single column counts, but I don't know how to adapt this to multiple columns of data. Thanks.

I have a problem which is beyond my capabilities. I need excel to sum amount column (See example table below) by unique values in company column then conditionally sum those values based on the year of investment, stage of company and finally, the kicker, the summed Amount by unique companies value has to be less than a certain threshold.

For example, I want companies in Stage Column of Exit only and in year 2004 only BUT only if the sum of the Amounts by unique company values is less than 100. So in the example table this would return 0. I have code and criteria written to sum unique values based on similar criteria such as sum if company Stage is Seed and Year is 2004 but for the life of me I can't seem to make the jump further.


For Unique values I used this function:
=SUMPRODUCT(($B$2:$B2=Year04)*($D$2:$D2=StageS)*($A$2:$A2=$A2))=1
Then:
=DCOUNTA($A$1:$D9999,2, Criteria)
or
=DSUM($A$1:$D9999,2, Criteria)

This along with criteria explained before returns the number of unique companies that are Stage=Seed and Year=2004 or sum of amounts with that criteria.

I am trying to adapt this technique to get what I want but to this point have been unsuccessful. My best guess is that I will need to create an array of the unique companies (New Sheet column A) with the Summed Amounts (New sheet column B) and then run functions again with the easier equations and criteria? I am trying to avoid combining company records as each investment needs to remain individual and there are 16000 records. Any help would be greatly appreciated.

Company Year Amount Stage XYZ 2004 10 Seed XYZ 2004 20 Seed ABC 2004 5 Early ABC 2004 25 Early DEV 2004 14 Later DEV 2004 19 Later TRU 2004 100 Exit TRU 2004 120 Exit TRU 2004 100 Exit

Using Excel 2010, creating a pivot table from about 25k rows of data. Trying to get a count of unique values. Data example:

State Color
AK Blue
AK Blue
AK Red
AR Blue
AR Yellow
AR Yellow
CO Red

Result:
AK Blue 2
AK Red 1
AR Blue 1
AR Yellow 2
CO Red 1

I've seen answers to the same question for Excel 2007, just not for 2010. Thanks in advance for your help!

MC

It's late, I'm tired, I'm sure it's ultra simple but my mind has totally blocked. How can I count the number of cells in a range that contain values that are greater than or equal to zero?

Thanks a bunch.

Hi All,

Is it possible to create a macro/equation that can count the amount of values over 50 for each second please?

I have been trying to figure this out with no luck. Any help would be greatly appreciated.
I have attached a worsheet detailing the problem. The outputs only have to show the number of values over 50 for each second.


Thanks,


Sean

Hi all,

I want to look to a range and if there is a value greater than zero I want to count it.
I keep going round in circles trying to do this and now I give up. How do I do this, please help?

I know this is probably a simple formula, but it's stumping me for some reason

I have account numbers and columns labled by day that has revenue listed under
I need to match up account numbers and count how many orders are greater than 300


acct # 1-May 2-May 3-May 100000 100 200 12 100002 600 900 3

I need to count how many are 300 and above for each account....
Any help is appreciated
Thanks

Hi
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
THanks

Hi,

Let's say I have a variable T = Cells(9,9).Value

I have defined a range called BlowUpChoiceOrder.

Within the following cells:

8 6 7 2 4 9 3 10 1 5
I am trying to find the number of values greater than T.

I have the following but it does not give me the right count.

VB:

 
T = Cells(Row, 18 + K).Value 
Set BlowUpChoiceOrder = Sheets("Sheet1").Range(Cells(Row, 8 + K), Cells(Row, 17)) 
Count = WorksheetFunction.CountIf(BlowUpChoiceOrder, ">" & T) 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



For example, if T = 8, the countif function should give me a result = to 2 as I have 9 and 10 afterwards but it does not give me that.

Any help?

Thanks