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).
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Pillar
[/TD]
[TD]Date Issued
[/TD]
[TD]Status
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AM
[/TD]
[TD]16-Jan-13
[/TD]
[TD]Closed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AM
[/TD]
[TD]26-Jan-13
[/TD]
[TD]Open
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Safety
[/TD]
[TD]4-Feb-13
[/TD]
[TD]Closed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM
[/TD]
[TD]11-Feb-13
[/TD]
[TD]Closed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AM
[/TD]
[TD]20-Feb-13
[/TD]
[TD]Closed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM
[/TD]
[TD]3-Mar-13
[/TD]
[TD]Open
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]Week 1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]PM Tags Issued
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM Tags Closed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AM Tags Issued
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AM Tags Closed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Safety Tags Issued
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Safety Tags Closed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Instead of trying to find the week number I just did the week of:

Sheet8

*ABCDEFGHIJK
*PillarDate IssuedStatus*******
*AMClosed*******
*AMOpen*******
*SafetyClosed*******
*PMClosed*******
*AMClosed*******
*PMOpen*******
***********
PM Tags Issued
PM Tags Closed
AM Tags Issued
AM Tags Closed
Safety Tags Issued
Safety Tags Closed

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]16-Jan-13[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]26-Jan-13[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]4-Feb-13[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]11-Feb-13[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]20-Feb-13[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]3-Mar-13[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]Week of:[/TD]
[TD="align: right"]1/5/2013[/TD]
[TD="align: right"]1/12/2013[/TD]
[TD="align: right"]1/19/2013[/TD]
[TD="align: right"]1/26/2013[/TD]
[TD="align: right"]2/2/2013[/TD]
[TD="align: right"]2/9/2013[/TD]
[TD="align: right"]2/16/2013[/TD]
[TD="align: right"]2/23/2013[/TD]
[TD="align: right"]3/2/2013[/TD]
[TD="align: right"]3/9/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</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
*
Pillar
Date Issued
Status
*
*
*
*
*
*
*
*
AM
Closed
*
*
*
*
*
*
*
*
AM
Open
*
*
*
*
*
*
*
*
Safety
Closed
*
*
*
*
*
*
*
*
PM
Closed
*
*
*
*
*
*
*
*
AM
Closed
*
*
*
*
*
*
*
*
PM
Open
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
PM Tags Issued
PM Tags Closed
AM Tags Issued
AM Tags Closed
Safety Tags Issued
Safety Tags Closed

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="align: right"]16-Jan-13
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

[TD="align: right"]26-Jan-13
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]

[TD="align: right"]4-Feb-13
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]

[TD="align: right"]11-Feb-13
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]

[TD="align: right"]20-Feb-13
[/TD]

[TD="bgcolor: #cacaca"]7
[/TD]

[TD="align: right"]3-Mar-13
[/TD]

[TD="bgcolor: #cacaca"]8
[/TD]

[TD="bgcolor: #cacaca"]9
[/TD]
[TD="align: right"]Week of:
[/TD]
[TD="align: right"]1/5/2013
[/TD]
[TD="align: right"]1/12/2013
[/TD]
[TD="align: right"]1/19/2013
[/TD]
[TD="align: right"]1/26/2013
[/TD]
[TD="align: right"]2/2/2013
[/TD]
[TD="align: right"]2/9/2013
[/TD]
[TD="align: right"]2/16/2013
[/TD]
[TD="align: right"]2/23/2013
[/TD]
[TD="align: right"]3/2/2013
[/TD]
[TD="align: right"]3/9/2013
[/TD]

[TD="bgcolor: #cacaca"]10
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="bgcolor: #cacaca"]11
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]12
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]15
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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
Date IssuedPillarStatus*****
AMClosed*****PM Tags Issued
AMOpen*****PM Tags Closed
SafetyClosed*****AM Tags Issued
PMClosed*****AM Tags Closed
AMClosed*****Safety Tags Issued
PMOpen*****Safety Tags Closed

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]Week of:[/TD]
[TD="align: right"]1/5/2013[/TD]
[TD="align: right"]1/12/2013[/TD]
[TD="align: right"]1/19/2013[/TD]
[TD="align: right"]1/26/2013[/TD]
[TD="align: right"]2/2/2013[/TD]
[TD="align: right"]2/9/2013[/TD]
[TD="align: right"]2/16/2013[/TD]
[TD="align: right"]2/23/2013[/TD]
[TD="align: right"]3/2/2013[/TD]
[TD="align: right"]3/9/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]16-Jan-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]26-Jan-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]4-Feb-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]11-Feb-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]20-Feb-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]3-Mar-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</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
Date Issued
Pillar
Status
*
*
*
*
*
AM
Closed
*
*
*
*
*
PM Tags Issued
AM
Open
*
*
*
*
*
PM Tags Closed
Safety
Closed
*
*
*
*
*
AM Tags Issued
PM
Closed
*
*
*
*
*
AM Tags Closed
AM
Closed
*
*
*
*
*
Safety Tags Issued
PM
Open
*
*
*
*
*
Safety Tags Closed

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="align: right"]Week of:
[/TD]
[TD="align: right"]1/5/2013
[/TD]
[TD="align: right"]1/12/2013
[/TD]
[TD="align: right"]1/19/2013
[/TD]
[TD="align: right"]1/26/2013
[/TD]
[TD="align: right"]2/2/2013
[/TD]
[TD="align: right"]2/9/2013
[/TD]
[TD="align: right"]2/16/2013
[/TD]
[TD="align: right"]2/23/2013
[/TD]
[TD="align: right"]3/2/2013
[/TD]
[TD="align: right"]3/9/2013
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]
[TD="align: right"]16-Jan-13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]
[TD="align: right"]26-Jan-13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]4
[/TD]
[TD="align: right"]4-Feb-13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]5
[/TD]
[TD="align: right"]11-Feb-13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]6
[/TD]
[TD="align: right"]20-Feb-13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]7
[/TD]
[TD="align: right"]3-Mar-13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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
Date IssuedPillarStatus*****
AMClosed*****PM Tags Issued
AMOpen*****PM Tags Closed
SafetyClosed*****AM Tags Issued
PMClosed*****AM Tags Closed
AMClosed*****Safety Tags Issued
PMOpen*****Safety Tags Closed
AMOpen****************

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]Week of:[/TD]
[TD="align: right"]1/5/2013[/TD]
[TD="align: right"]1/12/2013[/TD]
[TD="align: right"]1/19/2013[/TD]
[TD="align: right"]1/26/2013[/TD]
[TD="align: right"]2/2/2013[/TD]
[TD="align: right"]2/9/2013[/TD]
[TD="align: right"]2/16/2013[/TD]
[TD="align: right"]2/23/2013[/TD]
[TD="align: right"]3/2/2013[/TD]
[TD="align: right"]3/9/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]16-Jan-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]26-Jan-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]4-Feb-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]11-Feb-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]20-Feb-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]3-Mar-13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]16-Jan-13[/TD]

</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
991
samide2001
S
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
349
dilshod_k
D
M
  • Question Question
VBA code to create command button and copy criteria based data from one worksheet to another
2
Replies
13
Views
1K
Tupe77
T
D
  • Question Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
673
Herakles
H
k3yn0t3
  • Question Question
Formula to Sum Based on Criteria
2
Replies
19
Views
1K
k3yn0t3
k3yn0t3

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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