Count Formula

[wickedmosher]

Hello'

First time on the boards, just hoping someone can point me to a answer or a thread with an answer.

I am just passing the basics of Excel, I did my first VLOOKUP because of this site. Now I need help with a formula that I understand a small bit, but do not know how to get around to using for my needs.

I want to do some kind of a count formula that would count text, such as the names of colors, skip duplicates when they are grouped together, but count a duplicate if it shows up later in the column. I also have numbers in the same column, a 999, and I want to skip counting them as well. My Column would look like this, with side notes on what I want to count and skip.

Silver - count
Silver - skip
Black - count
Red - count
Silver - count
Blue - count
Blue - skip
999 - never count numbers
Silver - count
Purple - count

I use this kind of format on a schedule I work on for work and a formula for this would save me a lot of counting.

Please, any help would be great, and thanks in advance for helping me if you are able to.

 

[wickedmosher]

Well, I am back again. I have a new problem with the formula that I would love some help with. I have created a Box.net account so you can download the Excel spreadsheet with my example. Please, any help would be great.

http://www.box.net/shared/h13dqqgsln

Thanks in advance again, for any help that any of you can give me.

 

[Aladin Akyurek]

Well, I am back again. I have a new problem with the formula that I would love some help with. I have created a Box.net account so you can download the Excel spreadsheet with my example. Please, any help would be great.

http://www.box.net/shared/h13dqqgsln

Thanks in advance again, for any help that any of you can give me.

B2, copy down:

=IF(ISNUMBER(MATCH(A2,ColorList,0)),1-(A2=LOOKUP(REPT("z",255),$A$1:A1)),0)

ColorList refers to a range housing the admissible entries, in H2:H6, for exemple...

<TABLE style="WIDTH: 68pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=91><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3242" width=91><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=91>black</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>blue</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>purple</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>red</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>silver</TD></TR></TBODY></TABLE>

 

[Haseeb Avarakkan]

Or,

B2 copy down...

=AND(A2<>"",NOT(N(A2)),A2<>INDEX(A$1:A1,MATCH(REPT("Z",250),A$1:A1)))+0

 

[wickedmosher]

Both of these work just fine. Thanks again for all the help.

This board has been a huge help to my daily job. hope I can use what I have learned and help others as well like you guys have helped me.

 

[T. Valko]

Well, I am back again. I have a new problem with the formula that I would love some help with. I have created a Box.net account so you can download the Excel spreadsheet with my example. Please, any help would be great.

http://www.box.net/shared/h13dqqgsln

Thanks in advance again, for any help that any of you can give me.

Here's another one...

=--AND(A2<>"",ISTEXT(A2),A2<>LOOKUP("ZZZ",A$1:A1))

If the blank/empty cells DO NOT contain formula blanks then we can reduce that to:

=--AND(ISTEXT(A2),A2<>LOOKUP("zzz",A$1:A1))

 

[wickedmosher]

That one works too. You guys are all very good with Excel. Now I just got to get to the point were I can understand the formula that I am using so I can start writing them too.

Until then, I am very glad to have all of your help.Thanks again.

 

[T. Valko]

That one works too. You guys are all very good with Excel. Now I just got to get to the point were I can understand the formula that I am using so I can start writing them too.

Until then, I am very glad to have all of your help.Thanks again.

You're welcome. Thanks for the feedback!