SOLVED

Counting Table Entries Correctly When Filtering

Copper Contributor

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

@George_Weston 

 

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 (Copper Contributor)
Solution
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?
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.
1 best response

Accepted Solutions
best response confirmed by George_Weston (Copper Contributor)
Solution
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?

View solution in original post