Sum two columns of a pivottable into a third column

Question

excel file
pivottable screenshot

I have an Excel spreadsheet (above, with screenshot). There are three columns of data, A, B, and C. A fourth column, Combined, simply contains a formula that SUMs A and B (=SUM(B2:C2), etc). I want the pivottable to display value columns for:

count of non-zero rows in Combined
sum of rows in Combined
count of non-zero rows in C
sum of rows in C

Everything works except the first. I want it to display the data in column N, but I can't figure out how to either:

Sum together the counts of A and B
or get counts for Combined that don't take the 0 rows into account.

Solution

I can think of two ways to handle this.

First you could use two pivot tables. One pivot table uses the Combined column in the Report Filter to display only non-zero rows for your count and sum of Combined. The other pivot table uses Report Filter to display only non-zero rows for your count and sum of the C column. To do it this way, you need two pivot tables because once you filter out a row with Report Filter, it won't display anywhere on that pivot table.

The other way to handle this is to add columns in your data source so that no filtering is required. Since you only want to count non-zero rows in Combined, add a column called "combined count" with a formula like =if($D2<>0,1,0). Then in the pivot table, sum the "combined count" column to get the count of non-zero rows in Combined column. Then add another column to your data called "C count" with a formula like =if($E2<>0,1,0). Similarly, you'll be able to sum this column in the pivot table to get the count on non-zero rows in C. The other value columns will work normally.

Hope that helps!

Comments

[Q20]

You can directly upload images to this question.