counting data ignoring certain cells

this is probably two questions in one-
I have a matrix table with two columns that need cell counting with criteria.
Basically one formula will ignore any rows that have blank cells or a zero quantity. It will also ignore duplicated Part Numbers if any. What should be left are non-duplicated unique parts having a quantity of 1,2,...etc(no zeros). The final count should be 13 which is really a cell count.

The second formula does exactly the same but instead of only counting 1,2,.. etc it will also count the zero quantities 0,1,2, etc. The final count will be 15 for that one I attached a spreadsheet with data and result table-if you wish to help. what formulas should i use?- please.

Thanks.

not sure if my explantion is clear- i probably should replace the word 'or' with an 'and' - "blank cells AND zero quantity."

Originally Posted by Red fuji

Basically one formula will ignore any rows that have blank cells AND zero quantity. It will also ignore duplicated Part Numbers if any. What should be left are non-duplicated unique parts having a quantity of 1,2,...etc(no zeros). The final count should be 13 which is really a cell count.
Thanks.

How can i get these two formulas to look at the visible cells only and not the hidden ones ?? I am using autofiltering to exclude certain cells/rows from the calculation but the forumla doesnt care-it still counts them.

=SUM(IF(FREQUENCY(IF(ISNUMBER(1/Sheet1!$B$2:$B$26),MATCH(Sheet1!$A$2:$A$26,Sheet1!$A$2:$A$26,0)),ROW(Sheet1!$A$2:$A$26)-ROW(A$2)+1),1))

=SUM(IF(FREQUENCY(IF(Sheet1!$B$2:$B$26<>"",MATCH(Sheet1!$A$2:$A$26,Sheet1!$A$2:$A$26,0)),ROW(Sheet1!$A$2:$A$26)-ROW(A$2)+1),1))

the file is attached in first post.


Thanks.

In b1 the subtotal formula, for the visibel cells.

Also added a pivot table.

It can be very usefull for questions like this.

Ok - now i know that i need to include 'subtotal' and the '109' to exclude hidden values but where do i insert this in the jumbo formula; i tried but my limited experience didnt get me far.
SUBTOTAL(109,$a$3:$a$27) where should i insert this in the below formulas?


=SUM(IF(FREQUENCY(IF(ISNUMBER(1/Sheet1!$B$2:$B$26),MATCH(Sheet1!$A$2:$A$26,Sheet1!$A$2:$A$26,0)),ROW(Sheet1!$A$2:$A$26)-ROW(A$2)+1),1))

=SUM(IF(FREQUENCY(IF(Sheet1!$B$2:$B$26<>"",MATCH(Sheet1!$A$2:$A$26,Sheet1!$A$2:$A$26,0)),ROW(Sheet1! $A$2:$A$26)-ROW(A$2)+1),1))


Thanks.

Subtotal works based on a filter.

You filter a value (e.g. 1, 2), then subtotaal counts the visiable cells (in this example 1,2).

So I don't think you need to add the other formula.

I am not just counting visible cells;

I have two formulas that need tweeking. the difference between them is #1 includes zero qty parts into the count while #2 doesn’t. Both formulas function well except they need to ignore the hidden cells even if they have values in them (criteria 'c')

=SUM(IF(FREQUENCY(IF(Sheet1!$B$2:$B$26<>"",MATCH(Sheet1!$A$2:$A$26,Sheet1!$A$2:$A$26,0)),ROW(Sheet1! $A$2:$A$26)-ROW(A$2)+1),1))
This formula #1 needs to be revised to follow this criteria:
a. Ignores blank cells
b. ignores duplicated parts(it only countsone of them)
c. ignores hidden cells even if they have values in them.

=SUM(IF(FREQUENCY(IF(ISNUMBER(1/Sheet1!$B$2:$B$26),MATCH(Sheet1!$A$2:$A$26,Sheet1!$A$2:$A$26,0)),ROW(Sheet1!$A$2:$A$26)-ROW(A$2)+1),1))
This formula #2 needs to be revised to follow this criteria:
a. Ignores blank cells
b. ignores duplicated parts(it only counts one of them)
c. ignores hidden cells even if they have values in them.
d. ignores cells with '0' quantity


The sample file is attached in first post.

BTW- pivot tables are nice but they wont work for what i am looking for. Thanks for the help.

Thank you.

JosephP
I ran some tests on the actual data and your formula update seems to work. ( it is as long as a freight train but what the heck - it works.

Thank You.