Cumlative Counting Question

[Rob]

I was wondering if there is a way to write a formula that will count all the
unique and cumlative occurances of data in a spreadsheet? To try to say it
in another way... Count Non-Duplicate values per column per in each row
except for previous duplicates in predicessing columns on the given previous
rows.


Column
A B C D E
ROW
01 X Y X
02 X
03 X
04 X
05 X
06 X X
07 X Y
08 X X
09 X
10 X
11 X Y
12 X
13 X
14 X
15 X
16 X
17 X
18 Y X
19 X X
20 X X


A= 4 X's & 0 Y's

B= 12 X's & 2 Y's

C= 14 X's & 2 Y's

D= 14 X's & 3 Y's

E= 20 X's & 4 Y's


I hope that this is a possibility because I have to count items that way for
a few thound rows over a few hundred columns.


Thanks in Advance!

 

[ND Pard]

In HELP, lookup the CountIfs function ... it'll provide the desired results.

Good Luck.

 

[Rob]

I don't seem to have that available to me at all. I must apologize though...
I should have mentioned that I am using Excel 2003 for this and not 2007.
When I look for CountIfs on Microsoft Help it shouws up as an Office 07
feature.

So does that mean that there is no way of doing it in 2003?

Thanks for the reply.

 

[ND Pard]

Rob,

I believe the following steps will work if you follow them precisely.

My example is assuming the data is currently in cells A1 thru E20; you will
need to adjust the formulas where appropriate.

First, insert a new column "A"; this column MUST contain NO data.

Now your data resides in cells B1 thru F20.

Now insert a new worksheet.

My formulas assume your ORIGINAL worksheets is named: Sheet1.

On the NEW worksheet, in cell B1 enter the formula:

=IF(AND(OR('Sheet1'!B1="x",'Sheet1'!B1="y"),'Sheet1'!B1<>'Sheet1'!A1),IF('Sheet1'!B1="x",1,-1),0)

For best results, copy this formula into cell B1 of the new worksheet.

Now copy the formula in cell B1 to all cells in the range B1:F20 of the new
worksheet.

Finally, to sum your results enter the following formula in cell B22 of your
new worksheet and then copy it from B22 to F22.

=SUMIF($B$1:B$20,">0") & " Xs & " & -SUMIF($B$1:B$20,"<0") & " Ys"

Again, for best results, copy this formula into cell B22 of the new worksheet.


I believe this will provide the desired results.

Good Luck.

 

[Bernd P]

Hello,

http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd