excel charting formulas - sum of 2 columns with differing data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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/
_______
 
Back
Top