UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Formula Advice, Office 2003    
 
   
kapeller
post Apr 7 2012, 05:30 PM
Post #1

UtterAccess VIP
Posts: 2,630
From: Melbourne Australia Down Under
Hi

I seek some advice in relation to the formula below.
CODE
=COUNT(IF(FREQUENCY(MATCH(H6:H246,H6:H246,0),MATCH(H6:H246,H6:H246,0))>0,1))

The issue I am need to address, if possible, is that if the data within the range is less than H6 to H246 the value returned is zero “0”.

Am I stuck with this or is there a solution for this.


Many thanks
Go to the top of the page
 
+
norie
post Apr 7 2012, 06:27 PM
Post #2

UtterAccess VIP
Posts: 4,297
Lou

What's the purpose of the formula?
Go to the top of the page
 
+
kapeller
post Apr 7 2012, 06:53 PM
Post #3

UtterAccess VIP
Posts: 2,630
From: Melbourne Australia Down Under
Hi Norie

Sorry for not providing more information

What the formula is meant to do is match and then count how many occurrences a name appears in the range

EG Kapeller, Smith Browne and so on

See attached


Attached File  Count.zip ( 5.96K ) Number of downloads: 6
Go to the top of the page
 
+
norie
post Apr 7 2012, 06:59 PM
Post #4

UtterAccess VIP
Posts: 4,297
Lou

Perhaps I'm being thick, but couldn't you use COUNTIF for that?
Go to the top of the page
 
+
kapeller
post Apr 7 2012, 07:28 PM
Post #5

UtterAccess VIP
Posts: 2,630
From: Melbourne Australia Down Under
Hi Norie

Your are not thick. Its in my explaination.


It should only count kapeller as one ocurrence, browne occurence and so on.

Hope this helps
Go to the top of the page
 
+
JonSmith
post Apr 8 2012, 05:45 AM
Post #6

UtterAccess Guru
Posts: 594
Hi guys,

Correct me if I am wrong but you want to count the number of unique cells (in this case names) within a range.

If so you have the choice of this

CODE
=SUM(IF(FREQUENCY(MATCH(H6:H246,H6:H246,0),MATCH(H6:H246,H6:H246,0))>0,1,0))

(this will fail if any of the cells are blank however)


or an array formula, I am sure know what this are but for anyone who hasn't heard of them I won't go into detail (what Google is for) but to enter them you must type them out in the formula bar and then press Ctrl+Shift+Enter to save them. It will be wrapped in {} if you do it correctly.

This solution gets around the blanks problem

CODE
=SUM(IF(H6:H246<>"",1/COUNTIF(H6:H246,H6:H246)))


I cannot claim credit for these solutions btw, other people who are abit better at writing formula's worked it out before I got there.
Go to the top of the page
 
+
kapeller
post Apr 8 2012, 06:58 AM
Post #7

UtterAccess VIP
Posts: 2,630
From: Melbourne Australia Down Under
Hi Jon

Thank you for your help.

I have learnt a little more
QUOTE
or an array formula, I am sure know what this are but for anyone who hasn't heard of them I won't go into detail (what Google is for) but to enter them you must type them out in the formula bar and then press Ctrl+Shift+Enter to save them. It will be wrapped in {} if you do it correctly.


It does not matter who wrote the solution, you were able provide it to me.

I am very greatfull (IMG:style_emoticons/default/notworthy.gif)

Go to the top of the page
 
+
norie
post Apr 8 2012, 07:54 AM
Post #8

UtterAccess VIP
Posts: 4,297
Lou

Should have figured it might have been for unique names but it was a bit late when I posted last night. (IMG:style_emoticons/default/dazed.gif)

FWIW here's a non-array formula.
Go to the top of the page
 
+
JonSmith
post Apr 8 2012, 09:41 AM
Post #9

UtterAccess Guru
Posts: 594
Happy to help Lou and I'm glad I understood what you were after correctly.

See this for more info on array formula's. They can be useful and have a place but they are not exactly efficient when they run. Often a DCount or DSum will work better.
Go to the top of the page
 
+
kapeller
post Apr 8 2012, 04:29 PM
Post #10

UtterAccess VIP
Posts: 2,630
From: Melbourne Australia Down Under
Hi Jon

Thanks for this additional information.

QUOTE
See this for more info on array formula's. They can be useful and have a place but they are not exactly efficient when they run. Often a DCount or DSum will work better.
Go to the top of the page
 
+
kapeller
post Apr 8 2012, 04:30 PM
Post #11

UtterAccess VIP
Posts: 2,630
From: Melbourne Australia Down Under
Hi Norie

Many thanks for your input to this issue.
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 25th May 2013 - 04:21 AM