| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number and text values | Excel Discussion (Misc queries) | |||
| Counting number of times certain Text Values appear | Excel Discussion (Misc queries) | |||
| Assigning number values to text strings | Excel Discussion (Misc queries) | |||
| counting unique items(values or text) | Excel Worksheet Functions | |||
| Assigning text values to numeric fields | Excel Discussion (Misc queries) | |||
Linear Mode
