Counting text in multiple cells.

[Guest]

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

 

[Bob Phillips]

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)

 

[SteveG]

Assuming your data is in A1:B4 then,

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

Does that help?

Steve

 

[Guest]

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

 

[Guest]

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

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

 

[Guest]

thats perfect, than you very much

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)

 

[Guest]

thats perfect, than you very much

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

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

 

[Guest]

thats perfect, than you very much

 

[Guest]

thats perfect, than you very much

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