Assigning and counting values that are text

[MikeE]

Can anyone help me?
I have two columns, first one lists several types of sales as text; "NEW",
"NRE", "CPE" etc... the second column lists store numbers; "R18", "R41"
etc... I need a formula that will extract the desired information from the
data for each sales type to each store number. The data comes lumped
together, so I need to know how many "NEW" sales belong to "R18" for example.
Is this possible?

 

[John C]

Sumproduct, assuming Sales in column B, store number in column C, and dollar
amount in column C.

=SUMPRODUCT(--($B$2:$B$100="New"),--($C2:$C$100="R18"),($D$2:$D$100))

 

[Sheeloo]

Use PIVOT TABLE
Select your data (have first row as header)
Choose Data|Pivot table...|Next|Next| Click on Layout
Drag SaleType and StoreNo to the left column
You would see Count(...) in the right pane
Click Finish
Go to the new sheet

 

[Sheeloo]

Correction
Drag Type to the left col
and Store to the right side in the large area

 

[MikeE]

There is no $ value assigned. So the result just needs to count the all of
the "new" to the appropriate store.

 

[John C]

Then eliminate the $D$2:$D$100 argument. It would be just:
=SUMPRODUCT(($B$2:$B$100="New")*($C2:$C$100="R18"))

 

[MikeE]

That almost worked....
I need to break out the different sales type per location. The data looks
like this:

Sales Type Store No.
new R18
cpe R41
nre R18
new R41

So I need to seperate it by store and then by type.

NEW CPE NRE
R18 1 0 1
R41 1 1 0

Is this possible in a pivot table / or a via a formula?

 

[John C]

If you have your table set up like that:
New CPE NRE
R18
R41
....
Assuming this table starts in row 1, and column A, then in cell B2
(intersection of New and R18), type the following:
=SUMPRODUCT(($B$2:$B$100=A$1)*($C2:$C$100=$A2))
Then copy the formula to the right as far as needed, and down as far as
needed.

 

[Sheeloo]

Drag
Type to left col (ROW)
Strore also to top (COLUMN)
Store to central area (DATA) also