Use COUNTIF on SubTotal data

[phynds]

On the attached worksheet I have applied a Subtotal to my data. In a separate column I now want to do a count of each group of stock qty data that contains zero. I have used the COUNTIF Function in C10, but want to do the same in the total for each group. This is only a small amount of data, but in my original there are a large amount of products. Thank in advance for your help.

[Glenn Kennedy]

is it OK for me to put the subtotal in another column? Then it becomes very easy.

[phynds]

Yes, absolutely

[Glenn Kennedy]

Ok.

C2:
=if(countif($a$2:a2,a2)=countif(a:a,a2),countif(a:a,a2),"")

d2:
=if(countif($a$2:a2,a2)=countif(a:a,a2),countifs(a:a,a2,b:b,0),"")

[phynds]

Glenn, that is great, thank you. I'll just test it now on my original data.

[phynds]

Glenn, can you explain what you did to get to sub-total in COLUMN c?

[phynds]

Sorry Glenn, just noticed there was no need for the sub-total, you have used the CountIF.

[Glenn Kennedy]

Sure.

=if(countif($a$2:a2,a2)=countif(a:a,a2),countif(a:a,a2),"")

Red: if the incremental count of A2 (and it will be at the last (nth) occurrence of the value in A2 as youc copy down the table)
Orange: is equal to the total count in column A of the value in A2
Cyan: retutn the total count of the value in column A of the value in An... the last occurrence of that value.
Black otherwise retun a blank.

See columns G & H.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.