PIVOT TABLES: Show correct count value based on different cell values

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
64
Hello,

I have a pivot table that currently shows the following:

Column Labels
EXPIREDCOMING UP TO EXPIRATION
Row LabelsCount of Item CodeSum of Total ValueCount of Item CodeSum of Total Value
Tables74$817,00827$68,000
Chairs3$42219$900,000
Books18$254,00020$143,291

<tbody>
</tbody>


However, the issue I am having is that I don't want the "COUNT OF ITEM CODES" to include a count where the field in data source, "Quantity on Hand" = 0. While the SUM of Total Value would not be affected by this filter, I would want COUNT OF ITEM CODE to reflect correctly...see below:


Column Labels
EXPIREDCOMING UP TO EXPIRATION
Row LabelsCount of Item CodeSum of Total ValueCount of Item CodeSum of Total Value
Tables50$817,00827$68,000
Chairs1$42216$900,000
Books17$254,00020$143,291

<tbody>
</tbody>

Any help is greatly appreciated!

Thanks,
Jay
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
hello, Jay

Seeing the sample source data would help understanding.

I may be on the 'wrong track' but it sounds to me like excluding data with 'Quantity on Hand' = 0 is what you're after.
If so, can you put the 'Quantity on Hand' field as a page field and deselect the quantity 0?

HTH
 

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
64
Hi Fazza,

Wow, I think I had the biggest "brain fart" known to man. Yes, how I completely missed the fact that I could simply throw the Quantity on Hand field into the reports filter section & 'uncheck' 0, is beyond me. Thank you for giving me the virtual "slap on the back of the head". :)

Thank you & take care,
Jay
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Just a minor BF, Jay. :) Suggest you double-check the solution does exactly what you want. All the best, Fazza
 

Watch MrExcel Video

Forum statistics

Threads
1,102,784
Messages
5,488,858
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top