# Pivot Table: Count by subtracting neg. from pos.

#### ssalazarjr

##### Board Regular
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?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Marcelo Branco

##### MrExcel MVP
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

HTH

M

#### ssalazarjr

##### Board Regular
Your formula would work but I need to to happen in the Pivot table.

#### xenou

##### MrExcel MVP
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.

Replies
1
Views
134
Replies
6
Views
162
Replies
5
Views
107
Replies
1
Views
260
Replies
6
Views
374

1,127,742
Messages
5,626,618
Members
416,195
Latest member
tonmcg

### 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.

### Which adblocker are you using?

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

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