software

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

Why not just

=COUNTIF(B:B,quot;Wiganquot;)

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

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A20,{quot;ASDAquot;,quot;ALDIquot;},0))),--(B1:B20=quot;Wiganquot;)
)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

Assuming your data is in A1:B4 then,

=SUMPRODUCT(OR(A1:A4=quot;ALDIquot;,A1:A4=quot;ASDAquot;)*(B1:C4=quot; Wiganquot;))

Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=528540Try something like this:

With data in Col_A and Col_B

C1: =SUMPRODUCT((A1:A10={quot;ASDAquot;,quot;ALDIquot;})*(B1:B10=quot;WIGA Nquot;))

Adjust range references to suit your situation.

Does that help?

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

XL2002, WinXP-Proquot;Phil Jenkinsquot; wrote:

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

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

=SUMPRODUCT(--(A1:A100={quot;ASDAquot;, quot;ALDIquot;})*(B1:B100=quot;Wiganquot;))

quot;Phil Jenkinsquot; wrote:

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

thats perfect, than you very much

quot;Bob Phillipsquot; wrote:

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

thats perfect, than you very much

quot;Duke Careyquot; wrote:

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

thats perfect, than you very much

quot;Ron Coderrequot; wrote:

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

thats perfect, than you very much

quot;SteveGquot; wrote:

gt;
gt; Assuming your data is in A1:B4 then,
gt;
gt; =SUMPRODUCT(OR(A1:A4=quot;ALDIquot;,A1:A4=quot;ASDAquot;)*(B1:C4=quot; Wiganquot;))
gt;
gt; Does that help?
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=528540
gt;
gt;