Counting

V

Voodoodan

Hi all,

Can anyone tell me if there's a quick formula that can be done with th
following, please, to show only the amount of times 'apples' appears i
a row at the bottom of the list? For example:

apples
grapes
bananas
apples
pears
apples
apples
apples

The formula should show '3'.

This list would be updated with further fruity developments, so th
formula should be able to adapt to show only the times 'apples' appear
at the bottom of the list in a row.

Would be most appreciated if anyone knows the answer.

Many thanks,
Dan
 
A

Ardus Petus

My formula produces a wrong result (3) on following list:

apples
grapes
grapes
apples
apples
oranges
apples
apples


Sorry!
--
AP

Ardus Petus said:
=SUMPRODUCT((A1:A8="apples")*(A1:A8=A2:A9))

HTH
Click to expand...
 
V

Voodoodan

Hi,

The first two examples seem to produce all the results of "apples" i
the list. I just need the formula look at the column and calculate ho
many "apples" there are from the bottom of the list, until there's
change in the list, ie., with the below it will count only 3 apple
from the bottom because 'pears' is next in line.

apples
grapes
bananas
apples
pears
apples
apples
apples

If I decide to stick the following onto the end of this list:

bananas
apples
grapes
apples
apples

the formula would then need to be able to recalculate and show "2"
because there are only now 2 examples of apples from the bottom of th
list.

Hope this throws a bit more light on what I'm after!

Thanks v much,
Dan
 
D

Domenic

Try...

=COUNTIF(INDEX(A2:A9,MATCH(2,1/(A2:A9<>"Apples"))):A9,"Apples")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
V

Voodoodan

Hi,

That's good thanks, but it works only if you manually alter the formul
to show where the last cell is, ie., A9.

I was hoping that the formula would recalculate automatically, so tha
it somehow knows where the last entry is in the column, and works i
out from there.

Sorry for being a picky so and so!!

Thanks,
Dan
 
V

Voodoodan

Hi,

That's good thanks, but it works only if you manually alter the formula
to show where the last cell is, ie., A9.

I was hoping that the formula would recalculate automatically, so that
it somehow knows where the last entry is in the column, and works it
out from there.

Sorry for being a picky so and so!!

Thanks,
Dan.
 
D

Domenic

Try the following instead...

B2:

=MATCH(REPT("z",255),A:A)

C2:

=COUNTIF(INDEX(A2:INDEX(A:A,B2),MATCH(2,1/((A2:INDEX(A:A,B2)<>"")*(A2:IND
EX(A:A,B2)<>"Apples")))):INDEX(A:A,B2),"Apples")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
V

Voodoodan

That is spot on! Thank you very much for your efforts.

I'd love to know what the 'rept' function does here, but otherwis
that's worked beautifully.

Thank you,
Dan
 
D

Domenic

REPT ("z",255 ) is a large text value starting with the 26 English
letters. It's the largest text value recognized by MATCH and other
lookup functions. Since the 'match type' is omitted, it defaults to 1
and, therefore, doesn't look for an exact match. Used this way, it
returns the position of the last text value within a range.

Hope this helps!
 
Top