SOLVED

# Counting Table Entries Correctly When Filtering

Occasional Contributor

# Counting Table Entries Correctly When Filtering

Hi,

Hope someone can help me with this.  I have been going nuts for two days trying to use COUNT(), COUNTA(), COUNTIF(), COUNTIFS() and SUBTOTAL() in various ways to do what I thought was a simple calculation.

I have a Spare Parts table for my boat with multiple columns including "Desired Qty", "On-Hand Qty"  and "Qty to Order".

Above the table I have a Summary area where I want to display "Number of Entries", "Number of Entries with Parts On-Hand" and "Number of Entries Requiring Reorder".

I cannot get the "Number of Entries with Parts On-Hand" and the "Number of Entries Requiring Reorder" to calculate correctly when the table is unfiltered and filtered.   One of the issues occurs when there is a blank in a "Qty On-Hand" cell rather than a 0.

Would really appreciate if someone could help with this.  File is attached.

Thanks!

George

4 Replies

# Re: Counting Table Entries Correctly When Filtering

I think what you want is a conditional subtotal. See if the attached workbook is what you are trying to do.

best response confirmed by George_Weston (Occasional Contributor)
Solution

# Re: Counting Table Entries Correctly When Filtering

It works! Thank you very much. I briefly looked at the SUMPRODUCT function but couldn't understand it well enough to try to use it. I'm curious. What is the "--" used for in SUMPRODUCT?

# Re: Counting Table Entries Correctly When Filtering

It converts TRUE/FALSE to their underlying numerical values, 1 and 0. If you enter =--TRUE, you'll see it is change to 1 (and 0 for FALSE). The same thing happens when you perform explicit arithmetic operations with TRUE/FALSE, so TRUE+0, TRUE*1, etc. would accomplish the same thing.

There is an old white paper here that has a more detailed explanation:
http://xldynamic.com/source/xld.SUMPRODUCT.html

Thanks JMB.