Count and Sum and Summarize

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Item # PO # # of Action Date QTY remaining
Boxes
X4A341SB 9962 1 Out 4/16/2003 43
X4A341SB 9964 2 Out 4/16/2003 42
X4A341SB 9965 55 in 4/16/2003 41
X4A341SB 9967 6 Out 4/16/2003 40
89121 10001 1 Out 4/25/2003 19
BBK57911 10001 1 Out 4/25/2003 -42
CBK38161 10001 1 Out 4/25/2003 23
BBK33904 10001 2 Out 4/25/2003 -1
CBK48181 10001 6 in 4/25/2003 6
ABK03915 10001 8 in 4/25/2003 6
CBK03100 10008 1 Out 4/29/2003 49
CBK30000 10008 33 Out 4/29/2003 49
AB3L1011 10013 8 Out 4/29/2003 129
AB3L1011 10014 7 Out 4/29/2003 128
B48266 10015 4 Out 4/29/2003 32
B38266 10015 44 Out 4/29/2003 35

Hi everyone,
Once again I need some help and you guys are amazing so here goes.
I need to figure out how many boxes each po had and then figure out
how many po's had 2 boxes, 3 boxes etc. in a summary format. My issue
is that a po can have multiple items. I was trying to use a pivot
table, but I can figure out how to get the summary results. Don't
think I am being clear so the table below showes what the result is I
would like. Thanks again for the help.

X4A341SB 9962 1 Out 4/16/2003 43
X4A341SB 9964 2 Out 4/16/2003 42
X4A341SB 9967 6 Out 4/16/2003 40
89121 10001 5 Out 4/25/2003 19
CBK03100 10008 34 Out 4/29/2003 49
AB3L1011 10013 8 Out 4/29/2003 129
AB3L1011 10014 7 Out 4/29/2003 128
B48266 10015 48 Out 4/29/2003 32
 
You can create your summary with an AutoFilter, if you create another
column in your table:

1. Insert a new column, with the heading POCount
2. In cell below, enter a formula that refers to the first PO#:
=COUNTIF(B$2:B2,B2)
3. Copy this formula down to the last row of data
4. Select a cell in the table, and choose Data>Filter>AutoFilter
5. From the dropdown in the POCount heading, choose 1
6. From the dropdown in the Action heading, choose Out
 
Sorry -- I missed the column in which you want to sum the boxes. To do
this, add another column to the table, with the following formula:

=SUMPRODUCT(($B$2:$B$17=B2)*($E$2:$E$17="Out")*($D$2:$D$17))

where column B has the PO#, E is the Action, and D is the Box count.

Copy the formula down to the last row of data, then apply the AutoFilter
 
Back
Top