Multiple Criteria Count Statements

A

alibbye17

New Member
Joined
May 19, 2010
Messages
40
Hope someone can help or point me in the right direction. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
First the Data:<o:p></o:p>
<o:p></o:p>
Column (A)- Products<o:p></o:p>
Storage Unit A<o:p></o:p>
Storage Unit B<o:p></o:p>
Storage Unit C<o:p></o:p>
Storage Unit D<o:p></o:p>
<o:p></o:p>
Column (B) – Parts<o:p></o:p>
Wheels (A)<o:p></o:p>
Wheels (B)<o:p></o:p>
Steel Pipes<o:p></o:p>
Painted Pipes<o:p></o:p>
Aluminum Pipes<o:p></o:p>
<o:p></o:p>
Column (C) – Order Number<o:p></o:p>
<o:p></o:p>
Column (D) - QTY of Parts<o:p></o:p>
<o:p></o:p>
What Im trying to do is this:<o:p></o:p>
<o:p></o:p>
Example A (Column A, Column B, Column C, Column d)<o:p></o:p>
<o:p></o:p>
Storage Unit A, Wheels (A), 1902, 4<o:p></o:p>
Storage Unit A, Steel Pipes, 1902, 6<o:p></o:p>
Storage Unit A, Wheels (A), 1903, 4<o:p></o:p>
Storage Unit A, Steel Pipes, 1903, 6<o:p></o:p>
Storage Unit A, Wheels (B), 1904, 4<o:p></o:p>
Storage Unit A, Painted Pipes, 1904, 4<o:p></o:p>
<o:p></o:p>
I would first like to count the number of times a product shows up in an individual order. In the above example, Storage Unit A shows up 3 times (not 6, which is my problem with excel). And then I would like to know how many times each part is ordered with Storage Unit A. What im trying to do is see the correlation between products and parts.<o:p></o:p>
<o:p></o:p>
Final Summary:<o:p></o:p>
Storage Unit A = 3<o:p></o:p>
Wheels (A) = 2<o:p></o:p>
Wheels (B) = 1<o:p></o:p>
Steel Pipe = 2<o:p></o:p>
Painted Pipes = 1<o:p></o:p>
<o:p></o:p>
Is this possible?<o:p></o:p>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
alibbye17 said:
Hope someone can help or point me in the right direction. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
First the Data:<o:p></o:p>
<o:p></o:p>
Column (A)- Products<o:p></o:p>
Storage Unit A<o:p></o:p>
Storage Unit B<o:p></o:p>
Storage Unit C<o:p></o:p>
Storage Unit D<o:p></o:p>
<o:p></o:p>
Column (B) – Parts<o:p></o:p>
Wheels (A)<o:p></o:p>
Wheels (B)<o:p></o:p>
Steel Pipes<o:p></o:p>
Painted Pipes<o:p></o:p>
Aluminum Pipes<o:p></o:p>
<o:p></o:p>
Column (C) – Order Number<o:p></o:p>
<o:p></o:p>
Column (D) - QTY of Parts<o:p></o:p>
<o:p></o:p>
What Im trying to do is this:<o:p></o:p>
<o:p></o:p>
Example A (Column A, Column B, Column C, Column d)<o:p></o:p>
<o:p></o:p>
Storage Unit A, Wheels (A), 1902, 4<o:p></o:p>
Storage Unit A, Steel Pipes, 1902, 6<o:p></o:p>
Storage Unit A, Wheels (A), 1903, 4<o:p></o:p>
Storage Unit A, Steel Pipes, 1903, 6<o:p></o:p>
Storage Unit A, Wheels (B), 1904, 4<o:p></o:p>
Storage Unit A, Painted Pipes, 1904, 4<o:p></o:p>
<o:p></o:p>
I would first like to count the number of times a product shows up in an individual order. In the above example, Storage Unit A shows up 3 times (not 6, which is my problem with excel). And then I would like to know how many times each part is ordered with Storage Unit A. What im trying to do is see the correlation between products and parts.<o:p></o:p>
<o:p></o:p>
Final Summary:<o:p></o:p>
Storage Unit A = 3<o:p></o:p>
Wheels (A) = 2<o:p></o:p>
Wheels (B) = 1<o:p></o:p>
Steel Pipe = 2<o:p></o:p>
Painted Pipes = 1<o:p></o:p>
<o:p></o:p>
Is this possible?<o:p></o:p>
Click to expand...

Could you explain how you come up with Storage Unit A = 3??
 
Upvote 0
We have 3 different order numbers (1902,1903,1904) all containing the product Storage Unit A. Within the data I previously provided each order was duplicated. The reason for this duplication is that the information is shown at part level (showing every part included in the finished product). Example (below) – Column C indicates Order Number. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
For Order Number 1902 we have the following:<o:p></o:p>
<o:p> </o:p>
Storage Unit A<o:p></o:p>
With 4 Wheels (A)<o:p></o:p>
And with 6 Steel Pipes.<o:p></o:p>
<o:p> </o:p>
For Order Number 1904 we have the following:<o:p></o:p>
<o:p> </o:p>
Storage Unit A<o:p></o:p>
With 4 Wheels (B)<o:p></o:p>
And with 4 Painted Pipes.<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Reference Data:<o:p></o:p>
<o:p> </o:p>
Example A (Column A, Column B, Column C, Column d)

Storage Unit A, Wheels (A), 1902, 4
Storage Unit A, Steel Pipes, 1902, 6
Storage Unit A, Wheels (A), 1903, 4
Storage Unit A, Steel Pipes, 1903, 6
Storage Unit A, Wheels (B), 1904, 4
Storage Unit A, Painted Pipes, 1904, 4<o:p></o:p>
 
Upvote 0
alibbye17 said:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
....
Example A (Column A, Column B, Column C, Column d)<o:p></o:p>
<o:p></o:p>
Storage Unit A, Wheels (A), 1902, 4<o:p></o:p>
Storage Unit A, Steel Pipes, 1902, 6<o:p></o:p>
Storage Unit A, Wheels (A), 1903, 4<o:p></o:p>
Storage Unit A, Steel Pipes, 1903, 6<o:p></o:p>
Storage Unit A, Wheels (B), 1904, 4<o:p></o:p>
Storage Unit A, Painted Pipes, 1904, 4<o:p></o:p>
<o:p></o:p>
I would first like to count the number of times a product shows up in an individual order. In the above example, Storage Unit A shows up 3 times (not 6, which is my problem with excel). And then I would like to know how many times each part is ordered with Storage Unit A. What im trying to do is see the correlation between products and parts.<o:p></o:p>
<o:p></o:p>
Final Summary:<o:p></o:p>
Storage Unit A = 3<o:p></o:p>
Wheels (A) = 2<o:p></o:p>
Wheels (B) = 1<o:p></o:p>
Steel Pipe = 2<o:p></o:p>
Painted Pipes = 1<o:p></o:p>
<o:p></o:p>
Is this possible?<o:p></o:p>
Click to expand...

Let A2:D7 house the example sample you provide:

E2: Storage Unit A

F2, control+shift+enter, not just enter:
Code: 
=SUM(IF(FREQUENCY(IF($B$2:$B$7<>"",IF($A$2:$A$7=$A2,
    MATCH("~"&$B$2:$B$7,$B$2:$B$7&"",0))),
     ROW($B$2:$B$7)-ROW($B$2)+1),1))

E3: Wheels (A)

F3, if E2 is a precondition for counting this part:

=SUMPRODUCT(--($A$2:$A$7=E2),--($B$2:$B$7=E3))

If that's not the case, invoke just:

=COUNTIF($B$2:$B$7,E3)

Follow the set up that suits for other parts...
 
Upvote 0
alibbye17 said:
We have 3 different order numbers (1902,1903,1904) all containing the product Storage Unit A. Within the data I previously provided each order was duplicated. The reason for this duplication is that the information is shown at part level (showing every part included in the finished product). Example (below) – Column C indicates Order Number. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
For Order Number 1902 we have the following:<o:p></o:p>
<o:p> </o:p>
Storage Unit A<o:p></o:p>
With 4 Wheels (A)<o:p></o:p>
And with 6 Steel Pipes.<o:p></o:p>
<o:p> </o:p>
For Order Number 1904 we have the following:<o:p></o:p>
<o:p> </o:p>
Storage Unit A<o:p></o:p>
With 4 Wheels (B)<o:p></o:p>
And with 4 Painted Pipes.<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Reference Data:<o:p></o:p>
<o:p> </o:p>
Example A (Column A, Column B, Column C, Column d)

Storage Unit A, Wheels (A), 1902, 4
Storage Unit A, Steel Pipes, 1902, 6
Storage Unit A, Wheels (A), 1903, 4
Storage Unit A, Steel Pipes, 1903, 6
Storage Unit A, Wheels (B), 1904, 4
Storage Unit A, Painted Pipes, 1904, 4<o:p></o:p>
Click to expand...

OK. Thanks for the explanation I see now. Unfortunately, I don't have a solution though.
 
Upvote 0
Thanks Aladin. However, the formula in cell F2 brings back 4 units when it should bring back 3. I will try and digest the logic and apply a modification to your formula.

Thanks
 
Upvote 0
alibbye17 said:
Thanks Aladin. However, the formula in cell F2 brings back 4 units when it should bring back 3. I will try and digest the logic and apply a modification to your formula.

Thanks
Click to expand...

Storage Unit A, Wheels (A), 1902, 4
Storage Unit A, Steel Pipes, 1902, 6
Storage Unit A, Wheels (A), 1903, 4
Storage Unit A, Steel Pipes, 1903, 6
Storage Unit A, Wheels (B), 1904, 4
Storage Unit A, Painted Pipes, 1904, 4

Looks like the formula is right: Wheels (A), Steel Pipes, Wheels (B), Painted Pipes:laugh:

 
Upvote 0
Aladin, its close but not exact. The formula should bring back 3, indicating 3 orders for Storage Units A. I believe if you change the identifiers within your formula to reference column “C” you will come up with the correct answer. This will be the solution for step 1.

=SUM(IF(FREQUENCY(IF($C$2:$C$7<>"",IF($A$2:$A$7=$E2, MATCH("~"&$C$2:$C$7,$C$2:$C$7&"",0))), ROW($C$2:$C$7)-ROW($E$2)+1),1))
Click to expand...


Then I will apply the second formula you provided to each product to identify the count on parts used.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
Thanks again. You were a big help… As usual. <o:p></o:p>
 
Upvote 0
alibbye17 said:
Aladin, its close but not exact. The formula should bring back 3, indicating 3 orders for Storage Units A. I believe if you change the identifiers within your formula to reference column “C” you will come up with the correct answer. This will be the solution for step 1.
Click to expand...


If the range in C is the target, the answer is yes:

=SUM(IF(FREQUENCY(IF($C$2:$C$7<>"",IF($A$2:$A$7=$E2, MATCH("~"&$C$2:$C$7,$C$2:$C$7&"",0))), ROW($C$2:$C$7)-ROW($C$2)+1),1))

which still needs to be confirmed with control+shift+enter.





Then I will apply the second formula you provided to each product to identify the count on parts used.
Click to expand...
Hope it's the way you intended to have.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

Thanks again. You were a big help… As usual. <o:p></o:p>
Click to expand...
Thanks for the kind words and for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
419
STARL0RD
S
N
  • Solved
Pasting Value with VBA - Sorta Looping based on a multiple of 30
Replies
2
Views
410
nmbc99
N
B
  • Question Question
What's a formula that can do this?
Replies
2
Views
278
bcurrey
B
R
  • Solved
Find multiple matches with wildcard search
Replies
6
Views
538
Peter_SSs
Peter_SSs
zookeepertx
  • Question Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
382
zookeepertx
zookeepertx

Forum statistics

Threads
1,224,570
Messages
6,179,611
Members
452,931
Latest member
The Monk

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top