similair 2 condition sum

R

rcarrollct

heres the scenario, in one column I need to come up with how many store
of a specific type are in a given area, I have the area's in one colum
on ones sheet and the types in another column. so on a different shee
i need to sum the number of stores of one type in one area, but th
tricky part is i'm looking for general stores names like, shaws, o
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 th
"shaws" in the name and cound all of them with the specific area
need?? does that question make sense....
 
B

Biff

Hi!

Something like this:

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

Biff
 
E

Earl Kiosterud

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"))
 
M

Max

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
 
R

rcarrollct

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.
 
M

Max

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
Click to expand...

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.
Click to expand...

Assume this whole lot of text:
JEWEL-OSCO 3003 303 HOLMES AVE CLARENDON HILLS IL 60514 DU PAGE 8904 003
Click to expand...
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
 
Top