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

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

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

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

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

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