Thread: similair 2 condition sum

heres the scenario, in one column I need to come up with how many stores of a specific type are in a given area, I have the area's in one column on ones sheet and the types in another column. so on a different sheet i need to sum the number of stores of one type in one area, but the tricky part is i'm looking for general stores names like, shaws, or acme, and the listing has them as shaws/southeast or acme/southwest, acme/mountain. Is there a way to look for all the stores that have the "shaws" in the name and cound all of them with the specific area i need?? does that question make sense....?

Hi!

Something like this:

=SUMPRODUCT(--(A1:A100="area"),--(ISNUMBER(SEARCH("shaws",B1:B100))))

Biff

"rcarrollct" <rcarrollct.1rs1ic_1120701912.0509@excelforum-nospam.com> wrote
in message news:rcarrollct.1rs1ic_1120701912.0509@excelforum-nospam.com...
>
> heres the scenario, in one column I need to come up with how many stores
> of a specific type are in a given area, I have the area's in one column
> on ones sheet and the types in another column. so on a different sheet
> i need to sum the number of stores of one type in one area, but the
> tricky part is i'm looking for general stores names like, shaws, or
> acme, and the listing has them as shaws/southeast or acme/southwest,
> acme/mountain. Is there a way to look for all the stores that have the
> "shaws" in the name and cound all of them with the specific area i
> need?? does that question make sense....?
>
>
> --
> rcarrollct
> ------------------------------------------------------------------------
> rcarrollct's Profile:
> http://www.excelforum.com/member.php...o&userid=24931
> View this thread: http://www.excelforum.com/showthread...hreadid=385123
>

R,

A pivot table, using the count function, would give you all the groupings of
stores and areas. If you want to do it yourself with formulas, you might
use:

=SUMPRODUCT((A2:A10="acme")*(B2:B10="southwest"))
--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"rcarrollct" <rcarrollct.1rs1ic_1120701912.0509@excelforum-nospam.com> wrote
in message news:rcarrollct.1rs1ic_1120701912.0509@excelforum-nospam.com...
>
> heres the scenario, in one column I need to come up with how many stores
> of a specific type are in a given area, I have the area's in one column
> on ones sheet and the types in another column. so on a different sheet
> i need to sum the number of stores of one type in one area, but the
> tricky part is i'm looking for general stores names like, shaws, or
> acme, and the listing has them as shaws/southeast or acme/southwest,
> acme/mountain. Is there a way to look for all the stores that have the
> "shaws" in the name and cound all of them with the specific area i
> need?? does that question make sense....?
>
>
> --
> rcarrollct
> ------------------------------------------------------------------------
> rcarrollct's Profile:
> http://www.excelforum.com/member.php...o&userid=24931
> View this thread: http://www.excelforum.com/showthread...hreadid=385123
>

Try something like, in say C1:

=SUMPRODUCT((ISNUMBER(SEARCH("shaw",A2:A100))*(B2:B100="Area1")))

where A2:A100 contains the store names: Shaws, acme etc
and B2:B100 contains the area: Area1, Area2, etc
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"rcarrollct" <rcarrollct.1rs1ic_1120701912.0509@excelforum-nospam.com> wrote
in message news:rcarrollct.1rs1ic_1120701912.0509@excelforum-nospam.com...
>
> heres the scenario, in one column I need to come up with how many stores
> of a specific type are in a given area, I have the area's in one column
> on ones sheet and the types in another column. so on a different sheet
> i need to sum the number of stores of one type in one area, but the
> tricky part is i'm looking for general stores names like, shaws, or
> acme, and the listing has them as shaws/southeast or acme/southwest,
> acme/mountain. Is there a way to look for all the stores that have the
> "shaws" in the name and cound all of them with the specific area i
> need?? does that question make sense....?
>
>
> --
> rcarrollct
> ------------------------------------------------------------------------
> rcarrollct's Profile:
http://www.excelforum.com/member.php...o&userid=24931
> View this thread: http://www.excelforum.com/showthread...hreadid=385123
>

ok maybe my mistake, but the store names are in one column on sheet 2 as acme-south, acme-north,... i need a count of all the acme stores regardles of the south north whatever... in a seperate column there are numbers, so i need to find all the acme stores with that specific number... i.e.
JEWEL-OSCO 3003 303 HOLMES AVE CLARENDON HILLS IL 60514 DU PAGE 8904 003
ok thats how the columns look...
so on a seperate page, i need to find out how many stores are in the JEWEL area no matter what the -osco or whatever, and that fall in 003. any ideas, because the other solutions you gave me are really not what I need.

> the store names are in one column on sheet 2 as
> acme-south, acme-north,... i need a count of all the acme stores
> regardles of the south north whatever

Assume the names are in A2:A100 in Sheet2
In say, Sheet3, put in B2:
=SUMPRODUCT(--ISNUMBER(SEARCH("acme",Sheet2!A2:A100)))
This gives you the count of all "acme" stores
Note that case insensitivity is presumed (e.g. acme = ACME)

> .. in a seperate column there are numbers,
> so i need to find all the acme stores with that specific number... i.e.
> JEWEL-OSCO 3003 303 HOLMES AVE CLARENDON HILLS IL 60514 DU PAGE 8904 003
> ok thats how the columns look...
> so on a seperate page, i need to find out how many stores are in the
> JEWEL area no matter what the -osco or whatever, and that fall in 003.

Assume this whole lot of text:
> JEWEL-OSCO 3003 303 HOLMES AVE CLARENDON HILLS IL 60514 DU PAGE 8904 003
is in Sheet2, in a single cell B2
(with other similar text in B3, B4, .. up to B100)

In Sheet3, put in C2:

=SUMPRODUCT((ISNUMBER(SEARCH("acme",Sheet2!A2:A100)))*(ISNUMBER(SEARCH("jewe
l",Sheet2!B2:B100)))*(RIGHT(Sheet2!B2:B100,3)="003"))

This gives you the count of all "acme" stores in the "JEWEL" area that fall
in "003". Note that case insensitivity is presumed (e.g. JEWEL = jewel), and
the "003" is presumed to refer to the rightmost 3 digits at the end of the
text in B2 (or in col B) in Sheet2
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----