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

Counting text in multiple cells.
 

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 wrote in message
...
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

Counting text in multiple cells.
 

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

Counting text in multiple cells.
 

Try something like this:

With data in Col_A and Col_B

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

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

Counting text in multiple cells.
 

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

Counting text in multiple cells.
 

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 wrote in message
...
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

Counting text in multiple cells.
 

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

Counting text in multiple cells.
 

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="WIGA N"))

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

Counting text in multiple cells.
 

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