Question about Business & Productivity Software

1 Answer

Calculated field in Excel pivot

The Calculated field if used to calculated the difference between 2 fields gives the difference in amount. Is there a way to get the difference in count. The count formula in Calculated field doesn't seem to work. Kindly help

Posted by on

1 Answer

  • Level 1:

    An expert who has achieved level 1.


    An expert that gotĀ 5 achievements.


    An expert whose answer gotĀ voted for 20 times.


    An expert who has answered 20 questions.

  • Contributor
  • 28 Answers
Re: Calculated field in Excel pivot

Count will only work with numbers. So select another box where you want your number and use this formula: =count(x:x) This should give the count number then you will need to find the difference. In another box, use =sum(x-x). This should give you your difference.

If this helps you, please rate me. Thanks, Patty

Posted on Dec 30, 2007

Add Your Answer

0 characters

Uploading: 0%


Complete. Click "Add" to insert your video. Add


3 Points

Related Questions:

1 Answer

Insert a calculated field named % Change in Value to determine the percent change in values by type.

Assuming that the Original Value is in cell A1, and the New Value is in cell B1, then the formula for % Change pin Value is =(B1/A1)-1.Format the value in % style and you're done.Rgds,Scott

Sep 30, 2011 | Microsoft Excel for PC

1 Answer

Insert a calculated field named % Change in Value to determine the percent change in values by type in Excel?

If field A1 is the old value and A2 is the new value, then the formula for A3 (% change) is:=(A2-A1)/A1*100You can remove the *100 if you want to format the field to the Percent type.

Sep 30, 2011 | Microsoft Excel 2010

2 Answers

Count the number of open and closed order status per owner

My suggestion is to use a Pivot Table.  Follow these instructions:
Click on column A1 - then in the menu bar click DATA > Pivot Table and Pivot Chart Report.  

Click NEXT on step 1 of pivot table wizard.
For step two you will want to highlight all of the data on your sheet.  then click next.
Choose NEW WORKSHEET for step 3 of the pivot table wizard and click finish.
Once the Pivot Table sheet opens, drag the STATUS field into the top left corner of the pivot table view, where "drop column fields" and "drop row fields" meet.  Next drop another instance of the STATUS field into the "drop row fields here" column.  And lastly drop the OWNER field into the "drop column fields here" section.  You should now be viewing a report showing you exactly what you were looking for.

May 07, 2009 | Microsoft Excel for PC

1 Answer

Count with 2 or more criteria

Can you do this using a pivot table where columns B & C are Row Fields and Count of B&C is data fields.

Jan 17, 2009 | Microsoft Excel for PC

1 Answer


Hi, Pivot tables are quite complex but basically you have a table of data which you sort based on different fields from your table.

Send me your email address and I will email you further instructions with an attached example file ok.


Mar 01, 2008 | Business & Productivity Software

1 Answer

Data for excel chart

You need to create subtotals so that it looks like something like this:

Prod/units Amt. Level Total

xxxx 10.00 1
yyyy 20.00 1

dddd 40.00 2
ffff 30.00 2


To do this I would suggest using the Pivot Table feature of Excel. It is not too hard to use but you do have to know what you want and also have to be sure you are getting results as expected. Like any tool, it has to be used right. You can also use sub-total functions supplied in VBA as well in your cells.

If you want to avoid using VBA functions in cell and deal with ranges, etc., use the Pivot Table Wizard.

Some reading:

Also Google "Excel pivot table wizard" to get more.

Good luck!

Feb 23, 2008 | Microsoft Office Standard for PC

1 Answer

Export data in excel shld yoeet through VB

When i first figured out how to pull data from SQL and put the results in an excel file i referenced these two articles....
Reading and writing excel file using VB.NET (
Get the Values From DataBase and Stored into excell Sheet (

This is the code i ended up using.... (check out those links to see how you need to import the ms office excel reference file with visual basic)

Const stcon As String = "Provider=SQLNCLI;server=xxxxx;database=xxxxx;uid=xxxxx;pwd=xxxxx;DataTypeCompatibility=80"
Dim stSQL As String = "select * from scs_rate_class_money where irate_book = 124 and snew_used = 'U' and sclass = '2' and splan = 'T4' and sopt_code = 'F1'"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
'Open the connection.

'Open the recordset.
With rst
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.Open(stSQL, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly , _
.ActiveConnection = Nothing 'Disconnect the Recordset.
End With
'Close the connection
cnt.Close ()
Dim exp As Export = New Export()
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet )
Dim xlWSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim xlRange As Microsoft.Office.Interop.Excel.Range = CType(xlWSheet, Microsoft.Office.Interop.Excel.Worksheet).Range("A2")
Dim xlCalc As Microsoft.Office.Interop.Excel.XlCalculation
Dim i As Short

'Turn off Excel's calculation.
With xlApp
xlCalc = .Calculation
.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
End With
'Write the fieldnames.
For Each fld In rst.Fields
xlRange.Offset(0, i).Value = fld.Name
i = i + 1
'Populate the range.
xlRange.Offset(1, 0).CopyFromRecordset(rst)
'Close the recordset.
'Make Excel available to the user.
With xlApp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlCalc
End With
'Release variables from memory.
fld = Nothing
rst = Nothing
cnt = Nothing
xlRange = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing

Jan 03, 2008 | Business & Productivity Software

1 Answer

Microsoft Access

Create a hidden calculated field to extract the month from the date and use that for sorting and/or grouping. You should be able to group on the month as well, if that is the primary purpose. Thanks for rating FixYa!

Sep 30, 2007 | Microsoft Office Standard for PC

1 Answer


Prepare table having coloumns Client, Premium Amount , Month.


Now take Pivot table of the above table. Take Field 'Month' in Page field and Client in 'Row' field and sum of amount in 'Data field'.


If you refresh the data and select the required month you will get the desired information.




Sep 11, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Business & Productivity Software Logo

Related Topics:

874 people viewed this question

Ask a Question

Usually answered in minutes!

Top Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers


Level 3 Expert

18258 Answers

Sudeep Chatterjee
Sudeep Chatterjee

Level 3 Expert

3267 Answers

Are you a Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides