formula that can count the data in a cell but if the data are the same they count as 1 ??

[jasond1992]

formula that can count the data in a cell but if the data are the same they count as 1 ?? anybody can helpme. please.. godbless

[quekbc]

Perhaps something like this.

Note: this is an array formula, so type the formula in then press CTRL SHIFT ENTER.

Please Login or Register  to view this content.

[jasond1992]

i have the result this sir.

#DIV/0!

[jasond1992]

sir here's what hppen wen i input.....

[quekbc]

Ahh, that's from the blank cells methinks.

Try this one instead. Changes highlighted in red.

Again, CTRL SHIFT ENTER
=SUM(IFERROR(1 / COUNTIF(B4:B400, B4:B400),0))

[jasond1992]

Thank you so much sir. can i ask what is the different between array formula and formula only ?

[quekbc]

An array formula, is a formula that has "multiple" outputs.

For example, in a standard COUNTIF function, it usually returns 1 output (e.g. COUNTIF(Fruits , Apple)) will return you 1 number representing the number of apples in the basket of fruits.

What I've done above is to do something like COUNTIF(Fruits , {Apple, Banana}), which has 2 outputs, first being the number of apples, and second being the number of bananas. In this case, you'll need to select 2 cells, and type in the formula with the CTRL SHIFT ENTER to see both answers.

I've added 1 more step, which is to combine all the outputs, and that is using the SUM function. So, some examples:

1) COUNTIF(Fruits , Apple) = x --> not an array formula
2) COUNTIF(Fruits , Banana) = y --> not an array formula
3) COUNTIF(Fruits, {Apple, Banana}) = {x , y} --> is an array formula
4) SUM(COUNTIF(Fruits, {Apple, Banana})) = SUM({x,y}) = x + y = z --> not an array formula... but
because (3) is an array formula, you still need to tell Excel to process the data as an array formula (CTRL SHIFT ENTER)

[jasond1992]

aahhhh> Thankyou so much sir "quekbc"
This "{}" is an array formula if i use this ? or simply us ( CTRL SHIFT ENTER )

[quekbc]

Oh, which one are you talking about? The {} that I used in my examples above? or the one that you see when you type CTRL SHIFT ENTER?

In Excel, when you type a formula and you do CTRL SHIFT ENTER, it will automatically put the {}s in, e.g. {=SUM(ROW(1:10))}. These curly brackets should only be done by doing CTRL SHIFT ENTER.