Assigning and counting values that are text
 

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?

Assigning and counting values that are text
 

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


--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"MikeE" wrote:

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?

Assigning and counting values that are text
 

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

"MikeE" wrote:

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?

Assigning and counting values that are text
 

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

"MikeE" wrote:

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?

Assigning and counting values that are text
 

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

"John C" wrote:

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


--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"MikeE" wrote:

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?

Assigning and counting values that are text
 

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

--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"MikeE" wrote:

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

"John C" wrote:

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


--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"MikeE" wrote:

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?

Assigning and counting values that are text
 

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?

"Sheeloo" wrote:

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

"MikeE" wrote:

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?

Assigning and counting values that are text
 

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.

--
** John C **



"MikeE" wrote:

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?

"Sheeloo" wrote:

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

"MikeE" wrote:

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?

Assigning and counting values that are text
 

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

"MikeE" wrote:

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?

"Sheeloo" wrote:

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

"MikeE" wrote:

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?