Autopopulate and sum data based on criteria into another worksheet.

L

LWithers

New Member
Joined
Mar 14, 2013
Messages
10
Ok, so I have two charts here that track TAGS that we write for our presses. I would like a formula or code to be able to pull data from the first chart to populate the second chart in another worksheet. I want the formula/code to have an "if" statement to determine whether the "Pillar" is AM, PM, or Safety, and then have an "if" statement determining what week the "Date Issued" falls into. Then, with another "if" statement, I need it to determine whether the "Status" is "Open" or "Closed" and have it populate the data into the corresponding cell on the other chart.
Since there is only one cell corresponding to each week, I would then need the formula/code to sum the total number of tags in each week with the same criteria (PM open for example).
Pillar
Date Issued
Status
AM
16-Jan-13
Closed
AM
26-Jan-13
Open
Safety
4-Feb-13
Closed
PM
11-Feb-13
Closed
AM
20-Feb-13
Closed
PM
3-Mar-13
Open

<TBODY>
</TBODY>

Week 1
2
3
4
5
6
7
8
9
PM Tags Issued
PM Tags Closed
AM Tags Issued
AM Tags Closed
Safety Tags Issued
Safety Tags Closed

<TBODY>
</TBODY>
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Instead of trying to find the week number I just did the week of:

Sheet8

*ABCDEFGHIJK
1*PillarDate IssuedStatus*******
2*AM16-Jan-13Closed*******
3*AM26-Jan-13Open*******
4*Safety4-Feb-13Closed*******
5*PM11-Feb-13Closed*******
6*AM20-Feb-13Closed*******
7*PM3-Mar-13Open*******
8***********
9Week of:1/5/20131/12/20131/19/20131/26/20132/2/20132/9/20132/16/20132/23/20133/2/20133/9/2013
10PM Tags Issued0000001001
11PM Tags Closed0000001000
12AM Tags Issued0011000100
13AM Tags Closed0010000100
14Safety Tags Issued0000010000
15Safety Tags Closed0000000000

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C9=B9+7
B10{=SUMPRODUCT(($B$2:$B$7=TRIM(LEFT($A10,FIND(" ",$A10))))*((($C$2:$C$7<=B$9)*$C$2:$C$7)=(($C$2:$C$7>=(B$9-6))*$C$2:$C$7)))}
B11{=SUMPRODUCT(($B$2:$B$7=LEFT($A11,2))*((($C$2:$C$7<=B$9)*$C$2:$C$7)=(($C$2:$C$7>=(B$9-6))*$C$2:$C$7))*($D$2:$D$7=TRIM(RIGHT($A11,LEN($A11)-FIND("Closed",$A11)+1))))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

B10:B11 can be selected and dragged to B15, then dragged across.

There is probably a more elegant way, but this is mine :)
 
Upvote 0
Stan_C said:
Instead of trying to find the week number I just did the week of:

Sheet8

*
A
B
C
D
E
F
G
H
I
J
K
1
*
Pillar
Date Issued
Status
*
*
*
*
*
*
*
2
*
AM
16-Jan-13
Closed
*
*
*
*
*
*
*
3
*
AM
26-Jan-13
Open
*
*
*
*
*
*
*
4
*
Safety
4-Feb-13
Closed
*
*
*
*
*
*
*
5
*
PM
11-Feb-13
Closed
*
*
*
*
*
*
*
6
*
AM
20-Feb-13
Closed
*
*
*
*
*
*
*
7
*
PM
3-Mar-13
Open
*
*
*
*
*
*
*
8
*
*
*
*
*
*
*
*
*
*
*
9
Week of:
1/5/2013
1/12/2013
1/19/2013
1/26/2013
2/2/2013
2/9/2013
2/16/2013
2/23/2013
3/2/2013
3/9/2013
10
PM Tags Issued
1
1
11
PM Tags Closed
1
12
AM Tags Issued
1
1
1
13
AM Tags Closed
1
1
14
Safety Tags Issued
1
15
Safety Tags Closed

<TBODY>
</TBODY>

Spreadsheet Formulas
Cell
Formula
C9
=B9+7
B10
{=SUMPRODUCT(($B$2:$B$7=TRIM(LEFT($A10,FIND(" ",$A10))))*((($C$2:$C$7<=B$9)*$C$2:$C$7)=(($C$2:$C$7>=(B$9-6))*$C$2:$C$7)))}
B11
{=SUMPRODUCT(($B$2:$B$7=LEFT($A11,2))*((($C$2:$C$7<=B$9)*$C$2:$C$7)=(($C$2:$C$7>=(B$9-6))*$C$2:$C$7))*($D$2:$D$7=TRIM(RIGHT($A11,LEN($A11)-FIND("Closed",$A11)+1))))}

<TBODY>
</TBODY>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4

B10:B11 can be selected and dragged to B15, then dragged across.

There is probably a more elegant way, but this is mine :)
Click to expand...


Tag_Chart.jpg
[/URL][/IMG]

If my data is set up like that how would the formulas look?
 
Upvote 0
Just an FYI: Post your data how it is displayed ;)

You will need to expand the ranges in the formula to fit your columns A,B,C..

Sheet8

*ABCDEFGHIJKLMNOPQRS
1Date IssuedPillarStatus*****Week of:1/5/20131/12/20131/19/20131/26/20132/2/20132/9/20132/16/20132/23/20133/2/20133/9/2013
216-Jan-13AMClosed*****PM Tags Issued0000001001
326-Jan-13AMOpen*****PM Tags Closed0000001000
44-Feb-13SafetyClosed*****AM Tags Issued0011000100
511-Feb-13PMClosed*****AM Tags Closed0010000100
620-Feb-13AMClosed*****Safety Tags Issued0000010000
73-Mar-13PMOpen*****Safety Tags Closed0000000000

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:76px;"><col style="width:45.6px;"><col style="width:55.2px;"><col style="width:23.2px;"><col style="width:23.2px;"><col style="width:23.2px;"><col style="width:23.2px;"><col style="width:23.2px;"><col style="width:121.6px;"><col style="width:71.2px;"><col style="width:71.2px;"><col style="width:71.2px;"><col style="width:71.2px;"><col style="width:71.2px;"><col style="width:71.2px;"><col style="width:71.2px;"><col style="width:71.2px;"><col style="width:71.2px;"><col style="width:71.2px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
K1=J1+7
J2{=SUMPRODUCT(($B$2:$B$7=TRIM(LEFT($I2,FIND(" ",$I2))))*((($A$2:$A$7<=J$1)*$A$2:$A$7)=(($A$2:$A$7>=(J$1-6))*$A$2:$A$7)))}
J3{=SUMPRODUCT(($B$2:$B$7=LEFT($I3,2))*((($A$2:$A$7<=J$1)*$A$2:$A$7)=(($A$2:$A$7>=(J$1-6))*$A$2:$A$7))*($C$2:$C$7=TRIM(RIGHT($I3,LEN($I3)-FIND("Closed",$I3)+1))))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
Stan_C said:
Just an FYI: Post your data how it is displayed ;)

You will need to expand the ranges in the formula to fit your columns A,B,C..

Sheet8

*
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
1
Date Issued
Pillar
Status
*
*
*
*
*
Week of:
1/5/2013
1/12/2013
1/19/2013
1/26/2013
2/2/2013
2/9/2013
2/16/2013
2/23/2013
3/2/2013
3/9/2013
2
16-Jan-13
AM
Closed
*
*
*
*
*
PM Tags Issued
1
1
3
26-Jan-13
AM
Open
*
*
*
*
*
PM Tags Closed
1
4
4-Feb-13
Safety
Closed
*
*
*
*
*
AM Tags Issued
1
1
1
5
11-Feb-13
PM
Closed
*
*
*
*
*
AM Tags Closed
1
1
6
20-Feb-13
AM
Closed
*
*
*
*
*
Safety Tags Issued
1
7
3-Mar-13
PM
Open
*
*
*
*
*
Safety Tags Closed

<TBODY>
</TBODY>

Spreadsheet Formulas
Cell
Formula
K1
=J1+7
J2
{=SUMPRODUCT(($B$2:$B$7=TRIM(LEFT($I2,FIND(" ",$I2))))*((($A$2:$A$7<=J$1)*$A$2:$A$7)=(($A$2:$A$7>=(J$1-6))*$A$2:$A$7)))}
J3
{=SUMPRODUCT(($B$2:$B$7=LEFT($I3,2))*((($A$2:$A$7<=J$1)*$A$2:$A$7)=(($A$2:$A$7>=(J$1-6))*$A$2:$A$7))*($C$2:$C$7=TRIM(RIGHT($I3,LEN($I3)-FIND("Closed",$I3)+1))))}

<TBODY>
</TBODY>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...


If my columns with date, pillar, and status go all the way down to ABC66, does that need to be included in the formula somewhere?
 
Upvote 0
I manipulated the formula to cover all of the cells down to row 66, but it is not differentiating between open and closed so for every AM tag (for example) it tallies it under both open and closed. So for every PM, AM, or Safety tag it is actually showing two; one in the appropriate status cell and one in the other one as well. Any way to get around this?
 
Upvote 0
LWithers said:
If my columns with date, pillar, and status go all the way down to ABC66, does that need to be included in the formula somewhere?
Click to expand...

Rich (BB code): 
=SUMPRODUCT(($B$2:$B$66=TRIM(LEFT($I2,FIND(" ",$I2))))*((($A$2:$A$66<=J$1)*$A$2:$A$66)=(($A$2:$A$66>=(J$1-6))*$A$2:$A$66)))

Rich (BB code): 
=SUMPRODUCT(($B$2:$B$66=LEFT($I3,2))*((($A$2:$A$66<=J$1)*$A$2:$A$66)=(($A$2:$A$66>=(J$1-6))*$A$2:$A$66))*($C$2:$C$66=TRIM(RIGHT($I3,LEN($I3)-FIND("Closed",$I3)+1))))


Formulas need to be entered using: ctrl+shft+enter giving you the {} around the formula
LWithers said:
I manipulated the formula to cover all of the cells down to row 66, but it is not differentiating between open and closed so for every AM tag (for example) it tallies it under both open and closed. So for every PM, AM, or Safety tag it is actually showing two; one in the appropriate status cell and one in the other one as well. Any way to get around this?
Click to expand...

Are the formulas entered as shown above?
 
Last edited:
Upvote 0
Stan_C said:
Rich (BB code): 
=SUMPRODUCT(($B$2:$B$66=TRIM(LEFT($I2,FIND(" ",$I2))))*((($A$2:$A$66<=J$1)*$A$2:$A$66)=(($A$2:$A$66>=(J$1-6))*$A$2:$A$66)))

Rich (BB code): 
=SUMPRODUCT(($B$2:$B$66=LEFT($I3,2))*((($A$2:$A$66<=J$1)*$A$2:$A$66)=(($A$2:$A$66>=(J$1-6))*$A$2:$A$66))*($C$2:$C$66=TRIM(RIGHT($I3,LEN($I3)-FIND("Closed",$I3)+1))))


Formulas need to be entered using: ctrl+shft+enter giving you the {} around the formula



Are the formulas entered as shown above?
Click to expand...



Formulas are exact to what you wrote. I notice in the second formula you have the criteria "closed" right in the formula, but the first one doesn't include this for "open", is that the problem maybe? I'm not sure why it's tallying each tag as both open and closed.

 
Upvote 0
I misread your first post. Give this a go...

Sheet8

*ABCDEFGHIJKLMNOPQRS
1Date IssuedPillarStatus*****Week of:1/5/20131/12/20131/19/20131/26/20132/2/20132/9/20132/16/20132/23/20133/2/20133/9/2013
216-Jan-13AMClosed*****PM Tags Issued0000000001
326-Jan-13AMOpen*****PM Tags Closed0000001000
44-Feb-13SafetyClosed*****AM Tags Issued0011000000
511-Feb-13PMClosed*****AM Tags Closed0010000100
620-Feb-13AMClosed*****Safety Tags Issued0000000000
73-Mar-13PMOpen*****Safety Tags Closed0000010000
816-Jan-13AMOpen****************

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
K1=J1+7
J2{=SUMPRODUCT(($B$2:$B$66=TRIM(LEFT($I2,FIND(" ",$I2))))*((($A$2:$A$66<=J$1)*$A$2:$A$66)=(($A$2:$A$66>=(J$1-6))*$A$2:$A$66))*($C$2:$C$66="Open"))}
J3{=SUMPRODUCT(($B$2:$B$66=TRIM(LEFT($I3,FIND(" ",$I3))))*((($A$2:$A$66<=J$1)*$A$2:$A$66)=(($A$2:$A$66>=(J$1-6))*$A$2:$A$66))*($C$2:$C$66="Closed"))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
433
samide2001
S
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
158
dilshod_k
D
M
  • Question
VBA code to create command button and copy criteria based data from one worksheet to another
2
Replies
13
Views
474
Tupe77
T
D
  • Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
501
Herakles
H
k3yn0t3
  • Question
Formula to Sum Based on Criteria
2
Replies
19
Views
705
k3yn0t3
k3yn0t3

Forum statistics

Threads
1,214,994
Messages
6,122,633
Members
449,092
Latest member
bsb1122

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