Formula Help!!!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bishop8910 - 25 Nov 2006 22:30 GMT

i need a formula to count the amount of cells with the same number in the two
cells. at the moment im using =countif(range,"="&range) but when i use this
it counts the blank squares which i dont want it to. i cant enter text into
the boxes as i have other formulas using them cells. does anybody have and
ideas is there another formula or a modifyed version of my current one ????

Reply to this Message

T. Valko - 25 Nov 2006 22:54 GMT

Do you mean where 2 adjacent cells are equal?

Try this:

=SUMPRODUCT(--(A1:A5<>""),--(B1:B5<>""),--(A1:A5=B1:B5))

Biff

>i need a formula to count the amount of cells with the same number in the
>two
[quoted text clipped - 5 lines]
> ideas is there another formula or a modifyed version of my current one
> ????

Reply to this Message

Bishop8910 - 25 Nov 2006 23:18 GMT

A B C
D E
1 score    Group A
score
2    No Team vs Thames Pirates
3    Thames Pirates    vs    No Team
4     Stobswell Scary Monsters vs    Bracknell wanna Bees
5    Bracknell wanna Bees    vs    Stobswell Scary
6    Hoof Hearted    No Game
7

i want the formula to count the cells if the score in A2 is the same as in
E2. at the moment it counts the empty cells and i dont want it to. i need it
to count the cells which are 0-0 and any other score which is a draw.

> Do you mean where 2 adjacent cells are equal?
>
[quoted text clipped - 13 lines]
> > ideas is there another formula or a modifyed version of my current one
> > ????

Reply to this Message

T. Valko - 25 Nov 2006 23:24 GMT

Just change the range references:

=SUMPRODUCT(--(A2:A7<>""),--(E2:E7<>""),--(A2:A7=E2:E7))

Biff

> A B C
> D E
[quoted text clipped - 31 lines]
>> > ideas is there another formula or a modifyed version of my current one
>> > ????

Reply to this Message

daddylonglegs - 26 Nov 2006 00:24 GMT

Of course if both cells are equal you only really need to check that one is
not blank so this formula would suffice

=SUMPRODUCT(--(A2:A7<>""),--(A2:A7=E2:E7))

> Just change the range references:
>
[quoted text clipped - 37 lines]
> >> > ideas is there another formula or a modifyed version of my current one
> >> > ????

Reply to this Message

T. Valko - 26 Nov 2006 02:40 GMT

>if both cells are equal you only really need to check that one is
>not blank

Yeah, you're right!

Biff

> Of course if both cells are equal you only really need to check that one
> is
[quoted text clipped - 50 lines]
>> >> > one
>> >> > ????

Reply to this Message