not sure how to count an array on letters!?

[Guest]

hi,

i've got a 3 work sheets containing a month of a year, which is then full of information which i need convert into a single spreadsheet.... each sheet contains the same fields, and may also contain the same data for each month.
the problem is, writing a formula that will only count the data once, and not 2 or 3 times

So for example, if a customer was entered into october & november spreadsheet, it would only be recognised as 1, and not 2...

the cells can contain either numbers or letters, depending on the field...

i've been using this formula to calculate the number of clients
SUM(IF(FREQUENCY($A$3:$A$72,$A$3:$A$72)>0,1)

i've also added a field that seperates the clients by number, so that particular number can only be used by that client!
could this be used to define, whether it's a duplicate or not

hopefully i've explained it ok, pretty hard when the file doesn't appear in front of you

thank you for your hel

 

[Harlan Grove]

...
...

the problem is, writing a formula that will only count the data once, and not
2 or 3 times.

So for example, if a customer was entered into october & november spreadsheet,
it would only be recognised as 1, and not 2....

Meaning this customer would only flow into October rather than both October and
November?

the cells can contain either numbers or letters, depending on the field....

What cells? Some sort of customer ID? Details, please.

i've been using this formula to calculate the number of clients:
SUM(IF(FREQUENCY($A$3:$A$72,$A$3:$A$72)>0,1))

OK, but this would only work if all entries were numbers. It's safer to use

=SUMRODUCT(($A$3:$A$72<>"")/COUNTIF($A$3:$A$72,$A$3:$A$72&""))

which counts everything except blank cells and cells evaluating to "".

i've also added a field that seperates the clients by number, so that
particular number can only be used by that client! could this be used to
define, whether it's a duplicate or not.

Yes, using either of the formulas above.

hopefully i've explained it ok, pretty hard when the file doesn't appear in
front of you )

No, you need to give more details. If you're trying to pull a single customer ID
into a summary worksheet from entries on three separate worksheets, it can be
done, but the formulas are more complicated than those above. Also, formulas
aren't the best way to do this. If the data on the different monthly worksheets
is organized in tabular format, it'd be easier to add a column in each to hold
the month to which the data corresponds, then combine the tables from the three
worksheets - including the new column containing month - into a 'master' table
in a new worksheet. Then use Data > Filter > Advanced Filter to pull the
distinct customer IDs from the master table.