Hi all,

Can anyone tell me if there's a quick formula that can be done with the
following, please, to show only the amount of times 'apples' appears in
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 the
formula should be able to adapt to show only the times 'apples' appears
at the bottom of the list in a row.

Would be most appreciated if anyone knows the answer.

Many thanks,
Dan.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=525630

Hi

Have a look at COUNTIF()
=COUNTIF(A2:A28,"apples")

Andy.

"Voodoodan" wrote
in message ...

Hi all,

Can anyone tell me if there's a quick formula that can be done with the
following, please, to show only the amount of times 'apples' appears in
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 the
formula should be able to adapt to show only the times 'apples' appears
at the bottom of the list in a row.

Would be most appreciated if anyone knows the answer.

Many thanks,
Dan.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile:
http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=525630

=COUNTIF(A1:A3,A1)

where A1:A3 is the range, and A1 is what you are counting

"Voodoodan" wrote:


Hi all,

Can anyone tell me if there's a quick formula that can be done with the
following, please, to show only the amount of times 'apples' appears in
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 the
formula should be able to adapt to show only the times 'apples' appears
at the bottom of the list in a row.

Would be most appreciated if anyone knows the answer.

Many thanks,
Dan.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=525630

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

HTH
--
AP

"Voodoodan" a écrit
dans le message de
...

Hi all,

Can anyone tell me if there's a quick formula that can be done with the
following, please, to show only the amount of times 'apples' appears in
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 the
formula should be able to adapt to show only the times 'apples' appears
at the bottom of the list in a row.

Would be most appreciated if anyone knows the answer.

Many thanks,
Dan.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile:
http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=525630

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

apples
grapes
grapes
apples
apples
oranges
apples
apples


Sorry!
--
AP

"Ardus Petus" a écrit dans le message de
...
=SUMPRODUCT((A1:A8="apples")*(A1:A8=A2:A9))

HTH
--
AP

"Voodoodan" a
écrit
dans le message de
...

Hi all,

Can anyone tell me if there's a quick formula that can be done with the
following, please, to show only the amount of times 'apples' appears in
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 the
formula should be able to adapt to show only the times 'apples' appears
at the bottom of the list in a row.

Would be most appreciated if anyone knows the answer.

Many thanks,
Dan.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile:
http://www.excelforum.com/member.php...nfo&userid=597
View this thread:
http://www.excelforum.com/showthread...hreadid=525630

Hi,

The first two examples seem to produce all the results of "apples" in
the list. I just need the formula look at the column and calculate how
many "apples" there are from the bottom of the list, until there's a
change in the list, ie., with the below it will count only 3 apples
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 the
list.

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

Thanks v much,
Dan.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=525630

Try...

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

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

Hope this helps!

In article ,
Voodoodan
wrote:

Hi,

The first two examples seem to produce all the results of "apples" in
the list. I just need the formula look at the column and calculate how
many "apples" there are from the bottom of the list, until there's a
change in the list, ie., with the below it will count only 3 apples
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 the
list.

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

Thanks v much,
Dan.

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.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=525630

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.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=525630

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!

In article ,
Voodoodan
wrote:

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.

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

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

Thank you,
Dan.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=525630

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!

In article ,
Voodoodan
wrote:

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

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

Thank you,
Dan.