excel charting formulas - sum of 2 columns with differing data
 

I need help with creating a formula.

i have 2 columns in my data i need to sum in my chart, 1 column is the sales
reps name the other column is staus. when a project i work on becomes an
order i type the word "order" in the status column, but i cant get the 2
columns to add up in my chart.
what im trying to do is every time a given sales reps name appears in one
column and the word "order" appears in the other column in the same row.
i need to have a sum of how many times this appears, this gives me the
amount of orders any one sales rep has generated.

any help would be greatly appreciated, i would be glad to forward my spread
sheet to any one that would like to look it over and help me out.

John Watson

try
=sumproduct(--(Sales rep range = "Sales rep"),--(Status range = "Order")
the --) changes the logic true false to a numeric 1 0
the arrays in each section must be the same size, but the shorthand full
column (A:A) won't work.

"John Watson @ barkoff container" wrote:

I need help with creating a formula.

i have 2 columns in my data i need to sum in my chart, 1 column is the sales
reps name the other column is staus. when a project i work on becomes an
order i type the word "order" in the status column, but i cant get the 2
columns to add up in my chart.
what im trying to do is every time a given sales reps name appears in one
column and the word "order" appears in the other column in the same row.
i need to have a sum of how many times this appears, this gives me the
amount of orders any one sales rep has generated.

any help would be greatly appreciated, i would be glad to forward my spread
sheet to any one that would like to look it over and help me out.

John Watson

Assuming your data looks like this:

Sales Rep Status
Fred Order
Bill Order
Fred No Order
Fred Order
Bill Order
Bill No Order
Fred No Order
Bill Order

Select the data and make a Pivot Table (Data menu Pivot Table Report).
Drag teh Sales Rep field to the Row area and the Status field to the
Column area (or switch the two to transpose the table). Now drag the
Status field (another copy of it) to the Data area. You should get a
pivot table like this:

Count of Status Status
Sales Rep Order No Order Grand Total
Bill 3 1 4
Fred 2 2 4
Grand Total 5 3 8

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


John Watson @ barkoff container wrote:

I need help with creating a formula.

i have 2 columns in my data i need to sum in my chart, 1 column is the sales
reps name the other column is staus. when a project i work on becomes an
order i type the word "order" in the status column, but i cant get the 2
columns to add up in my chart.
what im trying to do is every time a given sales reps name appears in one
column and the word "order" appears in the other column in the same row.
i need to have a sum of how many times this appears, this gives me the
amount of orders any one sales rep has generated.

any help would be greatly appreciated, i would be glad to forward my spread
sheet to any one that would like to look it over and help me out.

John Watson