Pivot Table: Count by subtracting neg. from pos.

ssalazarjr

Board Regular
Joined
Jun 1, 2009
Messages
158
I have a spread sheet of individual sales/returns. The sales show as a positive dollar amount. The returns show as a negative dollar amount.

Summing them gives me the correct dollar amount.

But counting them is giving me the incorrect amount.

For example:

40
20
-20
10

should give me a count of 2 because the sale then return count as 0. But I get a count of 4 because its counting every number.

Any ideas how to fix this in a Pivot Table?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,487
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi,

I think the count offered by PVT = 4 is correct. Its just counting the number of occurrences - doesnt matter if the numbers are positives or negatives.

To get what you want maybe a formula like this
=COUNT(A1:A100)-SUM(IF(A1:A100<>"",--ISNUMBER(MATCH(A1:A100,-(A1:A100),0))))
Ctrl+Shift+Enter

(adjust the range to suit)

HTH

M
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
If you can add a column to the source data you can use a formula there:

=If(amount < 0, 1, 0)

Then you can sum this in your pivot table.

I'm not familiar enough with pivot table calculated fields but you might be able to use a similar formula as a calculated field.

In the end, your count really needs to be:
(Count of Positive values) - ((Count of negative values) * 2)

But using Sum will work if you have 1's and 0's to mark the values to be counted.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,725
Members
415,924
Latest member
togo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top