| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
BIG Dilemma.....HELP!!
I have some data that I am trying to do a function to calculate. What I want
to say but can't figure it out is.......If the sold to party is X,and the Category is Y, sum the net price total and return a Z. For instance, if the sold to party is "1009" and the category is "breakroom", sum the net price and return $14.18. I want to be able to fill down the formula because the data I am using is quite large and I have limited time to complete.... Sold-to pt Category Net Price Total 1009 Breakroom $5.89 1009 Breakroom $8.29 1009 Desk Accessories $15.96 1009 Desk Accessories $17.79 1009 File Storage $7.18 1009 File Storage $8.36 2235 Breakroom $235.86 2235 Desk Accessories $8.98 2235 File Storage $32.32 2616 Desk Accessories $1.83 2616 Desk Accessories $13.89 2616 File Storage $13.99 2616 File Storage $10.18 2616 File Storage $11.98 2749 Desk Accessories $0.61 2749 Desk Accessories $1.18 2749 File Storage $5.99 2749 File Storage $5.99 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
BIG Dilemma.....HELP!!
=SUMPRODUCT(--(A2:A100=1009),--(B2:B100="Y"),C2:C100)
adapt to fit accordingly -- Regards, Peo Sjoblom "Amber" wrote in message ... I have some data that I am trying to do a function to calculate. What I want to say but can't figure it out is.......If the sold to party is X,and the Category is Y, sum the net price total and return a Z. For instance, if the sold to party is "1009" and the category is "breakroom", sum the net price and return $14.18. I want to be able to fill down the formula because the data I am using is quite large and I have limited time to complete.... Sold-to pt Category Net Price Total 1009 Breakroom $5.89 1009 Breakroom $8.29 1009 Desk Accessories $15.96 1009 Desk Accessories $17.79 1009 File Storage $7.18 1009 File Storage $8.36 2235 Breakroom $235.86 2235 Desk Accessories $8.98 2235 File Storage $32.32 2616 Desk Accessories $1.83 2616 Desk Accessories $13.89 2616 File Storage $13.99 2616 File Storage $10.18 2616 File Storage $11.98 2749 Desk Accessories $0.61 2749 Desk Accessories $1.18 2749 File Storage $5.99 2749 File Storage $5.99 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
BIG Dilemma.....HELP!!
Could you clarify what the -- is?
"Peo Sjoblom" wrote: =SUMPRODUCT(--(A2:A100=1009),--(B2:B100="Y"),C2:C100) adapt to fit accordingly -- Regards, Peo Sjoblom "Amber" wrote in message ... I have some data that I am trying to do a function to calculate. What I want to say but can't figure it out is.......If the sold to party is X,and the Category is Y, sum the net price total and return a Z. For instance, if the sold to party is "1009" and the category is "breakroom", sum the net price and return $14.18. I want to be able to fill down the formula because the data I am using is quite large and I have limited time to complete.... Sold-to pt Category Net Price Total 1009 Breakroom $5.89 1009 Breakroom $8.29 1009 Desk Accessories $15.96 1009 Desk Accessories $17.79 1009 File Storage $7.18 1009 File Storage $8.36 2235 Breakroom $235.86 2235 Desk Accessories $8.98 2235 File Storage $32.32 2616 Desk Accessories $1.83 2616 Desk Accessories $13.89 2616 File Storage $13.99 2616 File Storage $10.18 2616 File Storage $11.98 2749 Desk Accessories $0.61 2749 Desk Accessories $1.18 2749 File Storage $5.99 2749 File Storage $5.99 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
BIG Dilemma.....HELP!!
Hi
Use a Pivot Table Mark the whole range of data. DataPivot TableNextFinish Drag the Sold to Party field to Page area Drag the Category to the Row area Drag the Net Price Total field to the Data area Select any party from the dropdown on the page field and see their purchases summarised by category. -- Regards Roger Govier "Amber" wrote in message ... I have some data that I am trying to do a function to calculate. What I want to say but can't figure it out is.......If the sold to party is X,and the Category is Y, sum the net price total and return a Z. For instance, if the sold to party is "1009" and the category is "breakroom", sum the net price and return $14.18. I want to be able to fill down the formula because the data I am using is quite large and I have limited time to complete.... Sold-to pt Category Net Price Total 1009 Breakroom $5.89 1009 Breakroom $8.29 1009 Desk Accessories $15.96 1009 Desk Accessories $17.79 1009 File Storage $7.18 1009 File Storage $8.36 2235 Breakroom $235.86 2235 Desk Accessories $8.98 2235 File Storage $32.32 2616 Desk Accessories $1.83 2616 Desk Accessories $13.89 2616 File Storage $13.99 2616 File Storage $10.18 2616 File Storage $11.98 2749 Desk Accessories $0.61 2749 Desk Accessories $1.18 2749 File Storage $5.99 2749 File Storage $5.99 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
BIG Dilemma.....HELP!!
This is great information. I am very unfamiliar with Pivot tables. Can I
then create a chart using the pivot table? "Roger Govier" wrote: Hi Use a Pivot Table Mark the whole range of data. DataPivot TableNextFinish Drag the Sold to Party field to Page area Drag the Category to the Row area Drag the Net Price Total field to the Data area Select any party from the dropdown on the page field and see their purchases summarised by category. -- Regards Roger Govier "Amber" wrote in message ... I have some data that I am trying to do a function to calculate. What I want to say but can't figure it out is.......If the sold to party is X,and the Category is Y, sum the net price total and return a Z. For instance, if the sold to party is "1009" and the category is "breakroom", sum the net price and return $14.18. I want to be able to fill down the formula because the data I am using is quite large and I have limited time to complete.... Sold-to pt Category Net Price Total 1009 Breakroom $5.89 1009 Breakroom $8.29 1009 Desk Accessories $15.96 1009 Desk Accessories $17.79 1009 File Storage $7.18 1009 File Storage $8.36 2235 Breakroom $235.86 2235 Desk Accessories $8.98 2235 File Storage $32.32 2616 Desk Accessories $1.83 2616 Desk Accessories $13.89 2616 File Storage $13.99 2616 File Storage $10.18 2616 File Storage $11.98 2749 Desk Accessories $0.61 2749 Desk Accessories $1.18 2749 File Storage $5.99 2749 File Storage $5.99 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
BIG Dilemma.....HELP!!
Hi Yes
Whilst in any cell on your PT, click on the Charts wizard on your toolbar and you will be taken to Charts. Again select any Customer from the Sold to field and see their data plotted. -- Regards Roger Govier "Amber" wrote in message ... This is great information. I am very unfamiliar with Pivot tables. Can I then create a chart using the pivot table? "Roger Govier" wrote: Hi Use a Pivot Table Mark the whole range of data. DataPivot TableNextFinish Drag the Sold to Party field to Page area Drag the Category to the Row area Drag the Net Price Total field to the Data area Select any party from the dropdown on the page field and see their purchases summarised by category. -- Regards Roger Govier "Amber" wrote in message ... I have some data that I am trying to do a function to calculate. What I want to say but can't figure it out is.......If the sold to party is X,and the Category is Y, sum the net price total and return a Z. For instance, if the sold to party is "1009" and the category is "breakroom", sum the net price and return $14.18. I want to be able to fill down the formula because the data I am using is quite large and I have limited time to complete.... Sold-to pt Category Net Price Total 1009 Breakroom $5.89 1009 Breakroom $8.29 1009 Desk Accessories $15.96 1009 Desk Accessories $17.79 1009 File Storage $7.18 1009 File Storage $8.36 2235 Breakroom $235.86 2235 Desk Accessories $8.98 2235 File Storage $32.32 2616 Desk Accessories $1.83 2616 Desk Accessories $13.89 2616 File Storage $13.99 2616 File Storage $10.18 2616 File Storage $11.98 2749 Desk Accessories $0.61 2749 Desk Accessories $1.18 2749 File Storage $5.99 2749 File Storage $5.99 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If Then Dilemma | Excel Worksheet Functions | |||
| Nth power dilemma | Excel Discussion (Misc queries) | |||
| The Prisoner's Dilemma | Excel Discussion (Misc queries) | |||
| Hyperlink Dilemma | Excel Discussion (Misc queries) | |||
| Spreadsheet Dilemma | Excel Discussion (Misc queries) | |||
Hybrid Mode
