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?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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
Back
Top