Pivot Table Calculated Field Formula

animas · Feb 19, 2010

<title>Excel Jeanie HTML</title>Pivot Table
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 92px;"> <col style="width: 87px;"> <col style="width: 98px;"> <col style="width: 119px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td> </td> <td>Values</td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Row Labels</td> <td>Sum of value</td> <td>Count of value</td> <td>Sum of Calculated</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: left;">100222</td> <td style="text-align: right;">0</td> <td style="text-align: right;">1</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: left;">100777</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: left;">100888</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: left;">100999</td> <td style="text-align: right;">0</td> <td style="text-align: right;">1</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="text-align: left;">100333</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="text-align: left;">Grand Total</td> <td style="text-align: right;">5</td> <td style="text-align: right;">7</td> <td style="text-align: right;">0</td></tr></tbody></table>
In above pivot table Sum of Calculated is the calculated field.
ID is the row field. Value is the data field.

I want to achieve something like below in this calculated field.
= IF(Sum of value=Count of value,1,0)

I tried = IF(Sum(value)=Count(value),1,0). But it didn't work.
What formula should I write in this Sum of Calculated field?

foverman · Feb 20, 2010

Hello Animas,

Looking around on MrExcel to research an answer, and I thought I was having deja vu when I came across this:
http://www.mrexcel.com/forum/showthread.php?t=448798

But Andrew Poulsom's answer is part of this solution. First, add his count in column C:

Excel Workbook

A

B

C

1

Row Label

Value

Counts

2

100222

0

3

100777

1

4

100777

1

5

100888

1

6

100888

1

7

100999

0

8

100456

1

9

100456

1

10

100456

1

11

100234

1

0

12

100234

1

0

13

100234

0

14

100678

1

15

100678

1

16

100678

1

17

100678

1

18

100333

1

Sheet1 (2)

Excel 2003

Cell Formulas
Range	Formula
C2	=IF(COUNTIF($A$2:$A$18,A2)=SUMIF($A$2:$A$18,A2,$B$2:$B$18),1,0)

Next Drag the Counts field onto the Pivot Table, right click Sum of Counts, select Summarize By Min, and you get the result below:

Excel Workbook

E

F

G

H

1

Data

2

Row Label

Sum of Value

Count of Value

Min of Counts

3

100222

0

1

0

4

100777

2

1

5

100888

2

1

6

100999

0

1

0

7

100333

1

8

100456

3

1

9

100234

2

3

0

10

100678

4

1

11

Grand Total

14

17

0

Sheet1 (2)

Excel 2003

Does that do what you want?

Fazza · Feb 20, 2010

Hi,

Suggest the work is done using SQL. I'll describe a manual approach.

Such as, give the source data a normal (not dynamic) named range "MyData". Save the file. From a separate Excel workbook, start the pivot table wizard and take the external data option at the first step. Follow the wizard to the end and take the option to edit in MS Query. Then hit the SQL button and edit the SQL to be like below. OK to enter it and see the results dataset, hit the 'open door' icon to return the data to Excel & complete the pivot table. If you like the resultant new worksheet can be moved into the source data workbook. If you don't need the sum & count in the final result, take them out of the first line of the SQL.

regards, Fazza

Code:

SELECT ID, MySum, MyCount, Iif(MySum=MyCount,1,0) AS [MyNewField]
FROM (
SELECT ID, Sum(value) AS [MySum], Count(value) AS [MyCount]
FROM MyData
GROUP BY ID)

animas · Feb 20, 2010

hi Fazza,
thanks for the ideas.

hi foverman,

i was trying to avoid inserting extra row with that formula each time i add new rows in pivot data source.

but your solution can do the trick without the formula you mentioned. i simply summarized default Sum Of Value as Min of Value and...

From a video i got another idea, i can summarize Sum Of Value as Product of Value. When there will be a single 0, result will be 0. When all values are 1, result will be 1.

But i liked your Min idea better and will be using in future solutions. Thanks again.

foverman · Feb 20, 2010

Fazza said:
Suggest the work is done using SQL. I'll describe a manual approach.

Hi Fazza,

This is a fascinating approach. I've only touched on SQL in other areas of our systems at work, and have never really done anything with it. I've never used the External Data option from Excel, and it gives me interesting ideas.

I followed your instructions to the point of View/Edit the SQL Statement Directly. When I enter

Code:

...
FROM (
SELECT ID, Sum(value) AS [MySum], Count(value) AS [MyCount]
...

It says SQL Query can't be represented graphically. Continue anyway?
then Could not add the table '('.

When I enter

Code:

...
FROM (SELECT ID, Sum(value) AS [MySum], Count(value) AS [MyCount]
...

It gives the graphically warning and then Could not add the table '(Select'.

Any suggestions on what I need to do differently? (Excel 2003, btw.)

foverman · Feb 20, 2010

Fazza,

As "only a self-taught novice at SQL" you certainly seem to contribute a lot about SQL! Thank you!

I've been attacking PHP because of a system I "inherited" at work, and I know that leads to MySQL, so this is taking me on a good path. I've spent some time at w3schools in PHP and JavaScript, but see I need to go to http://www.w3schools.com/sql/default.asp.

After working with this some more, I got this to work

Code:

SELECT MyData.ID, MyData.MySum, MyData.MyCount
FROM MyData MyData

So I think I've got the table and fields named as you have it, but still get the Could not add the table error when I try to paste in your SQL.

What am I missing?

Fazza · Feb 21, 2010

Hi, foverman

Without seeing the full SQL that you have, I can only guess. So, please post the entire SQL - every single character exactly as you have it. Even line breaks are important.

Some guesses. Missing final parenthesis. Lack of space or other separator between key words.

Some further explanation. You're right that the section you have working confirms the correct table & field names. Good. This is now used as the source data for the next level of query. It is done like this,

Code:

SELECT fields to select the second time
FROM (
the first bit that you have already, enclosed in parentheses)

Hence,

Code:

SELECT ID, MySum, MyCount, Iif(MySum=MyCount,1,0) AS [MyNewField]
FROM (
SELECT ID, MySum, MyCount
FROM MyData)

Have fun. F

foverman · Feb 21, 2010

Fazza,

What I have currently is

Code:

SELECT MyData.ID, MyData.MySum, MyData.MyCount
FROM MyData MyData

What I was trying to paste in from your first answer is

Code:

SELECT ID, MySum, MyCount, Iif(MySum=MyCount,1,0) AS [MyNewField]
FROM (
SELECT ID, Sum(value) AS [MySum], Count(value) AS [MyCount]
FROM MyData
GROUP BY ID)

but that gives the Could not add the table '(' error.

Fazza · Feb 21, 2010

I'm right in the middle of something else & will get back to this thread in 15 minutes or so.

Fazza · Feb 21, 2010

OK. When I posted on the weekend that was from home & it worked fine. When I tested it just now at work - Excel 2003 - it was also perfect.

I also copied & pasted from the end of your post #8. And it worked fine. That is, the SQL was solely & exactly from your most recent post.

I don't know what to say when it doesn't work for you, other than to try again in a fresh workbook.

HTH, F

Pivot Table Calculated Field Formula

animas

Active Member

Excel Facts

foverman

Board Regular

Fazza

MrExcel MVP

animas

Active Member

foverman

Board Regular

foverman

Board Regular

Fazza

MrExcel MVP

foverman

Board Regular

Fazza

MrExcel MVP

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

Pivot Table Calculated Field Formula

Active Member

Excel Facts

Board Regular

MrExcel MVP

Active Member

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock