LikeOffice    Excel Help

EXCEL Utility
Customized Ribbon

- Compare worksheets
- Database analysis
- Stock to your Excel
- Password recovery
- and more...
Excel Formula
List of Excel
Formula examples
 
Excel Question
Ask us an Excel Question

 

Sumproduct Lookup Sumif Countif Unique Percentile

 

1. Count unique values that appears more than 2 times    
2. Count unique items in a list    
3. Count unique items in a list with conditions [shelly]    
4. Count unique values that appears more than 2 times    
5. Count unique values that appears more than 2 times in column C and at least one time the value 'a' in column E    
6. Count unique values that appears more than 2 times    
7. Count unique items in a list    
8. Sum ONLY unique values    
9. Sum unique values    





1. Count unique values that appears more than 2 times

Count unique values that appears more than 2 times.


Array Formula:

{=SUM(IF(FREQUENCY(IF(RNG_C<>"",MATCH(RNG_C,RNG_C,0)),ROW(RNG_C)-ROW($C$1)+1)>2,1))}



Answer = 2

Top

2. Count unique items in a list

Count unique items in a list.


Array Formula:

{=SUM(IF(FREQUENCY(IF(LEN(RNG_C)>0,MATCH(RNG_C,RNG_C,0),""), IF(LEN(RNG_C)>0,MATCH(RNG_C,RNG_C,0),""))>0,1))}



Answer = 4

Top

3. Count unique items in a list with conditions [shelly]

Count unique items in a list with conditions [shelly].
It counts how many unique items are in 'Column C' subject to the conditions that there is 'b' in Column D.

Formula:

=SUMPRODUCT(((RNG_C<>"")/COUNTIF(RNG_C,RNG_C&""))*ISNUMBER(MATCH(RNG_C&E12,RNG_C&RNG_E,0)))

E12 = b

Answer = 1

Top

4. Count unique values that appears more than 2 times

Count unique values that appears more than 2 times.


Formula:

=SUMPRODUCT((RNG_C<>"")/COUNTIF(RNG_C,RNG_C&"")*(COUNTIF(RNG_C,RNG_C)>2))



Answer = 2

Top

5. Count unique values that appears more than 2 times in column C and at least one time the value 'a' in column E

Count unique values that appears more than 2 times in column C and at least one time the value 'a' in column E.


Formula:

=SUMPRODUCT(((RNG_C<>"")/COUNTIF(RNG_C,RNG_C&""))*(COUNTIF(RNG_C,RNG_C)>2)*ISNUMBER(MATCH(RNG_C&E12,RNG_C&RNG_E,0)))

E12 = a

Answer = 2

Top

6. Count unique values that appears more than 2 times

Count unique values that appears more than 2 times.


Formula:

=SUMPRODUCT(--(RNG_C<>""),--(COUNTIF(RNG_C,RNG_C)>2),--(MATCH(RNG_C&"",RNG_C&"",0)=ROW(RNG_C)-ROW($C$2)+1))



Answer = 2

Top

7. Count unique items in a list

Count unique items in a list.
It counts how many unique items are in 'Column C'

Formula:

=SUMPRODUCT(((RNG_C<>"")/COUNTIF(RNG_C,RNG_C&"")))



Answer = 4

Top

8. Sum ONLY unique values

Sum ONLY unique values.
It sum up only the cells tha appear one time in a list

Array Formula:

{=SUM(IF(COUNTIF(RNG_F, RNG_F)=1, RNG_F, ""))}



Answer = 45

Top

9. Sum unique values

Sum unique values.
It sum up all unique values in a list. If a value appear more than once then it takes it one time only.

Array Formula:

{=SUM(IF(IF(MATCH(RNG_F, RNG_F, 0)=(ROW(RNG_F)-ROW(F1048518)+1), (ROW(RNG_F)-ROW(F1048518)+1), 0)<>0, RNG_F, ""))}



Answer = 0

Top

10. Sum of 2 or more ranges using Sumproduct with + (plus) sign

Sum of 2 or more ranges using Sumproduct with + (plus) sign.


Formula:

=SUMPRODUCT((RNG_C=E12)+(RNG_E=F12),RNG_H)

E12 = Cola2
F12 = b

Answer = 202

Top
 




Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2011 LikeOffice - Privacy Policy - Contact us

[Top]