Count filtered items

**bad in excel** · 12-20-2007, 09:16 AM

Hello everyone,
I'm really not sure if you will understand my question or not becuase it is very hard to explain, but I'll give it a shot.

We are making products which has two parts, PART A & PART B. If both the Parts are purchased by the same individual we take it as one product and allocate a unique ID (Product ID) given to that product. So if Mr. A buys both the parts, we will say

Product ID - Part
1 - A
1 - B

If a customer busy one part only we still take it as a product and note which part of the product has been purchased. Say Mr. B buys Part B only, we will say.

Product ID - Part
2 - A

And then all the products & Parts are borken into Phases.

Based on the conept, I would like to have help in developing a formula for:
E5 to E8
I5 to I8
K5 to K8
M5 to M8
O5 to O8

I've attached the table in the .doc format when its taken in excell the table is perfectly placed. Thank you for your help, in advance.

**corinereyes** · 12-20-2007, 03:35 PM

Hello,

Maybe it will be better if you'll attach an excel file and the expected results in their corresponding cell.

**bad in excel** · 12-21-2007, 02:05 AM

: ) sorry, didnt occur to me that I can change it in .zip and post it.

**corinereyes** · 12-21-2007, 02:36 AM

Hello,

In cell E5 try this formula:

=COUNT(IF(FREQUENCY(IF($A$12:$A$39=D5,MATCH($B$12:$B$39,$B$12:$B$39,0)),ROW($A$12:$A$39)-ROW($A$12)+1),1))

select E5, press F2, then press CTRL+SHIFT+ENTER (formula in array)
then copy down up to E8.

In cell F5:

=COUNTIF($A$12:$A$39,D5)

copy down up to cell F8
Use the sum function for cell E9 and F9.

ok, here is my query, for the ranges:
I5 to I8
K5 to K8
M5 to M8
O5 to O8

would you like to count the total of each of the criteria in D5:D8 with matching PART Bs???? Can you explain this part a bit more because i've noticed that there are 4 sets of Part A and B.

See the attached file.

**bad in excel** · 12-21-2007, 09:45 AM

Hey corinereyes
First of all, big time thank you for helping me out : ) and putting so much effort in it.

In the attachement you will find that Process A for Product 1, 2 & 12 has been completed and the summary report shows it. I need the count in the summary report.
I would also like to know if there is any possibility this count changes as and when we put on the filters. Somthing like the subtotal but it keeps the count in the next to thier phases and everything.

Once again, thank you very much for helping me out. I really, really, really appreciate : )