Counting text in multiple cells.

Hi All,

Here's my problem. I know a bit about formula's on excel but this one has
got me stumped. For example: I have to different shops with many different
locations. The locations are in different cells to the shops.

Here's an Example
Each line is classed as 1 incident
ASDA Wigan
ASDA Skelmersdale
ALDI Wigan
ALDI Skelmersdale

I need to know how many occurences of asda wigan and aldi wigan. When i use
the =countif formula it deducts the answer from the first sum and uses it for
the second sum. I have hears of a way of entering a formula that tells me the
answer. I think it was something to do with asking asda = 1 where
skelmersdale occurs and asda = 1 where wigan occurs. Then the same for all
the others.

Please Help.

Phil Jenkins

Assuming your data is in A1:B4 then,

=SUMPRODUCT(OR(A1:A4="ALDI",A1:A4="ASDA")*(B1:C4="Wigan"))

Does that help?

Steve

Why not just

=COUNTIF(B:B,"Wigan")

If the example data is only part and you don't want other stores in Wigan,
use

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A20,{"ASDA","ALDI"},0))),--(B1:B20="Wigan")
)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil Jenkins" <Phil Jenkins@discussions.microsoft.com> wrote in message
news:5A5B3397-8C4E-41E7-AEC3-DF80331D67E9@microsoft.com...
> Hi All,
>
> Here's my problem. I know a bit about formula's on excel but this one has
> got me stumped. For example: I have to different shops with many different
> locations. The locations are in different cells to the shops.
>
> Here's an Example
> Each line is classed as 1 incident
> ASDA Wigan
> ASDA Skelmersdale
> ALDI Wigan
> ALDI Skelmersdale
>
> I need to know how many occurences of asda wigan and aldi wigan. When i
use
> the =countif formula it deducts the answer from the first sum and uses it
for
> the second sum. I have hears of a way of entering a formula that tells me
the
> answer. I think it was something to do with asking asda = 1 where
> skelmersdale occurs and asda = 1 where wigan occurs. Then the same for all
> the others.
>
> Please Help.
>
> Phil Jenkins

Try something like this:

With data in Col_A and Col_B

C1: =SUMPRODUCT((A1:A10={"ASDA","ALDI"})*(B1:B10="WIGAN"))

Adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Phil Jenkins" wrote:

> Hi All,
>
> Here's my problem. I know a bit about formula's on excel but this one has
> got me stumped. For example: I have to different shops with many different
> locations. The locations are in different cells to the shops.
>
> Here's an Example
> Each line is classed as 1 incident
> ASDA Wigan
> ASDA Skelmersdale
> ALDI Wigan
> ALDI Skelmersdale
>
> I need to know how many occurences of asda wigan and aldi wigan. When i use
> the =countif formula it deducts the answer from the first sum and uses it for
> the second sum. I have hears of a way of entering a formula that tells me the
> answer. I think it was something to do with asking asda = 1 where
> skelmersdale occurs and asda = 1 where wigan occurs. Then the same for all
> the others.
>
> Please Help.
>
> Phil Jenkins

to get the count of ASDA/Wigan and ALDI/Wigan use

=SUMPRODUCT(--(A1:A100={"ASDA", "ALDI"})*(B1:B100="Wigan"))

"Phil Jenkins" wrote:

> Hi All,
>
> Here's my problem. I know a bit about formula's on excel but this one has
> got me stumped. For example: I have to different shops with many different
> locations. The locations are in different cells to the shops.
>
> Here's an Example
> Each line is classed as 1 incident
> ASDA Wigan
> ASDA Skelmersdale
> ALDI Wigan
> ALDI Skelmersdale
>
> I need to know how many occurences of asda wigan and aldi wigan. When i use
> the =countif formula it deducts the answer from the first sum and uses it for
> the second sum. I have hears of a way of entering a formula that tells me the
> answer. I think it was something to do with asking asda = 1 where
> skelmersdale occurs and asda = 1 where wigan occurs. Then the same for all
> the others.
>
> Please Help.
>
> Phil Jenkins

thats perfect, than you very much

"Bob Phillips" wrote:

> Why not just
>
> =COUNTIF(B:B,"Wigan")
>
> If the example data is only part and you don't want other stores in Wigan,
> use
>
> =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A20,{"ASDA","ALDI"},0))),--(B1:B20="Wigan")
> )
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Phil Jenkins" <Phil Jenkins@discussions.microsoft.com> wrote in message
> news:5A5B3397-8C4E-41E7-AEC3-DF80331D67E9@microsoft.com...
> > Hi All,
> >
> > Here's my problem. I know a bit about formula's on excel but this one has
> > got me stumped. For example: I have to different shops with many different
> > locations. The locations are in different cells to the shops.
> >
> > Here's an Example
> > Each line is classed as 1 incident
> > ASDA Wigan
> > ASDA Skelmersdale
> > ALDI Wigan
> > ALDI Skelmersdale
> >
> > I need to know how many occurences of asda wigan and aldi wigan. When i
> use
> > the =countif formula it deducts the answer from the first sum and uses it
> for
> > the second sum. I have hears of a way of entering a formula that tells me
> the
> > answer. I think it was something to do with asking asda = 1 where
> > skelmersdale occurs and asda = 1 where wigan occurs. Then the same for all
> > the others.
> >
> > Please Help.
> >
> > Phil Jenkins
>
>
>

thats perfect, than you very much

"Duke Carey" wrote:

> to get the count of ASDA/Wigan and ALDI/Wigan use
>
> =SUMPRODUCT(--(A1:A100={"ASDA", "ALDI"})*(B1:B100="Wigan"))
>
> "Phil Jenkins" wrote:
>
> > Hi All,
> >
> > Here's my problem. I know a bit about formula's on excel but this one has
> > got me stumped. For example: I have to different shops with many different
> > locations. The locations are in different cells to the shops.
> >
> > Here's an Example
> > Each line is classed as 1 incident
> > ASDA Wigan
> > ASDA Skelmersdale
> > ALDI Wigan
> > ALDI Skelmersdale
> >
> > I need to know how many occurences of asda wigan and aldi wigan. When i use
> > the =countif formula it deducts the answer from the first sum and uses it for
> > the second sum. I have hears of a way of entering a formula that tells me the
> > answer. I think it was something to do with asking asda = 1 where
> > skelmersdale occurs and asda = 1 where wigan occurs. Then the same for all
> > the others.
> >
> > Please Help.
> >
> > Phil Jenkins

thats perfect, than you very much

"Ron Coderre" wrote:

> Try something like this:
>
> With data in Col_A and Col_B
>
> C1: =SUMPRODUCT((A1:A10={"ASDA","ALDI"})*(B1:B10="WIGAN"))
>
> Adjust range references to suit your situation.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Phil Jenkins" wrote:
>
> > Hi All,
> >
> > Here's my problem. I know a bit about formula's on excel but this one has
> > got me stumped. For example: I have to different shops with many different
> > locations. The locations are in different cells to the shops.
> >
> > Here's an Example
> > Each line is classed as 1 incident
> > ASDA Wigan
> > ASDA Skelmersdale
> > ALDI Wigan
> > ALDI Skelmersdale
> >
> > I need to know how many occurences of asda wigan and aldi wigan. When i use
> > the =countif formula it deducts the answer from the first sum and uses it for
> > the second sum. I have hears of a way of entering a formula that tells me the
> > answer. I think it was something to do with asking asda = 1 where
> > skelmersdale occurs and asda = 1 where wigan occurs. Then the same for all
> > the others.
> >
> > Please Help.
> >
> > Phil Jenkins

thats perfect, than you very much

"SteveG" wrote:

>
> Assuming your data is in A1:B4 then,
>
> =SUMPRODUCT(OR(A1:A4="ALDI",A1:A4="ASDA")*(B1:C4="Wigan"))
>
> Does that help?
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=528540
>
>