Hi all. I have searched the forum for a solution to this but it's not coming to me. I have two tables, one is a plan, and the other is a report. In the plan I have Work Orders, Team names and planned hours by week. In the report (references a pivot table elsewhere) I have a work order, and hours charged by team. In the pivot table, I can filter out certain teams, which will then leave that section of the table blank (Row 14 in his example).What I'd like to be able to do is pull the planned hours base on the what's on the report. If the report cell is blank, ignore it in the SUM of planned hours, else, include it. Here's my data:
[TABLE="width: 319"]
<colgroup><col width="64" style="width: 48pt;"><colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"><colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"><colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="2"><colgroup><col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A1[/TD]
[TD="width: 80, bgcolor: transparent"]B1[/TD]
[TD="width: 79, bgcolor: transparent"]C1[/TD]
[TD="width: 68, bgcolor: transparent"]D1[/TD]
[TD="width: 68, bgcolor: transparent"]E1[/TD]
[TD="width: 66, bgcolor: transparent"]F1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2[/TD]
[TD="bgcolor: transparent"]Plan[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3[/TD]
[TD="bgcolor: #44546A"]Work Order[/TD]
[TD="bgcolor: #44546A"]Team[/TD]
[TD="bgcolor: #44546A"]30-Sep-18[/TD]
[TD="bgcolor: #44546A"]7-Oct-18[/TD]
[TD="bgcolor: #44546A"]14-Oct-18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4[/TD]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"]Team 1[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]9.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Team 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]10.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]10.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]8.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A6[/TD]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"]Team 3[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A7[/TD]
[TD="bgcolor: transparent, align: right"]456[/TD]
[TD="bgcolor: transparent"]Team 1[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]4.00[/TD]
[TD="bgcolor: transparent"]12.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]456[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Team 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]12.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]10.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]6.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A9[/TD]
[TD="bgcolor: transparent, align: right"]456[/TD]
[TD="bgcolor: transparent"]Team 3[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]4.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A12[/TD]
[TD="bgcolor: transparent"]Report[/TD]
[TD="bgcolor: transparent"]Work Order[/TD]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A13[/TD]
[TD="bgcolor: #44546A"]Team[/TD]
[TD="bgcolor: #44546A"]30-Sep-18[/TD]
[TD="bgcolor: #44546A"]7-Oct-18[/TD]
[TD="bgcolor: #44546A"]14-Oct-18[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A14[/TD]
[TD="bgcolor: transparent"]Team 1[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Team 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A16[/TD]
[TD="bgcolor: transparent"]Team 3[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]9.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A17[/TD]
[TD="bgcolor: #44546A"]ACTUALS[/TD]
[TD="bgcolor: #44546A"]15.00
[/TD]
[TD="bgcolor: #44546A"]12.00[/TD]
[TD="bgcolor: #44546A"]17.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A18[/TD]
[TD="bgcolor: #44546A"]PLAN[/TD]
[TD="bgcolor: #44546A"]5.00
[/TD]
[TD="bgcolor: #44546A"]8.00[/TD]
[TD="bgcolor: #44546A"]0.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A19[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
In cell C18, I have only one Team criteria:
SUMIFS(INDEX($D$4:$F$9,,MATCH(C$13,$D$3:$F$3,0)),$B$4:$B$9,$D$12,$C$4:$C$9,IF(C$14="","<>",$B$14))
In cell D18, I have two Team criteria, one of which is blank. If I populate the blank team data, the formula does not work:
SUMIFS(INDEX($D$4:$F$9,,MATCH(D$13,$D$3:$F$3,0)),$B$4:$B$9,$D$12,$C$4:$C$9,IF(D$14="","<>",$B$14),$C$4:$C$9,IF(D$15="","<>",$B$15))
And last, in cell E8, I tried having all tree Team criteria, and as expected, it doesn't work either:
SUMIFS(INDEX($D$4:$F$9,,MATCH(E$13,$D$3:$F$3,0)),$B$4:$B$9,$D$12,$C$4:$C$9,IF(E$14="","<>",$B$14),$C$4:$C$9,IF(E$15="","<>",$B$15),$C$4:$C$9,IF(E$16="","<>",$B$16))
Is this something really simple that I'm missing? Thanks in advance.
[TABLE="width: 319"]
<colgroup><col width="64" style="width: 48pt;"><colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"><colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"><colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="2"><colgroup><col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A1[/TD]
[TD="width: 80, bgcolor: transparent"]B1[/TD]
[TD="width: 79, bgcolor: transparent"]C1[/TD]
[TD="width: 68, bgcolor: transparent"]D1[/TD]
[TD="width: 68, bgcolor: transparent"]E1[/TD]
[TD="width: 66, bgcolor: transparent"]F1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2[/TD]
[TD="bgcolor: transparent"]Plan[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3[/TD]
[TD="bgcolor: #44546A"]Work Order[/TD]
[TD="bgcolor: #44546A"]Team[/TD]
[TD="bgcolor: #44546A"]30-Sep-18[/TD]
[TD="bgcolor: #44546A"]7-Oct-18[/TD]
[TD="bgcolor: #44546A"]14-Oct-18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4[/TD]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"]Team 1[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]9.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Team 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]10.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]10.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]8.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A6[/TD]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"]Team 3[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A7[/TD]
[TD="bgcolor: transparent, align: right"]456[/TD]
[TD="bgcolor: transparent"]Team 1[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]4.00[/TD]
[TD="bgcolor: transparent"]12.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]456[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Team 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]12.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]10.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]6.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A9[/TD]
[TD="bgcolor: transparent, align: right"]456[/TD]
[TD="bgcolor: transparent"]Team 3[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]4.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A12[/TD]
[TD="bgcolor: transparent"]Report[/TD]
[TD="bgcolor: transparent"]Work Order[/TD]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A13[/TD]
[TD="bgcolor: #44546A"]Team[/TD]
[TD="bgcolor: #44546A"]30-Sep-18[/TD]
[TD="bgcolor: #44546A"]7-Oct-18[/TD]
[TD="bgcolor: #44546A"]14-Oct-18[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A14[/TD]
[TD="bgcolor: transparent"]Team 1[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Team 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A16[/TD]
[TD="bgcolor: transparent"]Team 3[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]9.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A17[/TD]
[TD="bgcolor: #44546A"]ACTUALS[/TD]
[TD="bgcolor: #44546A"]15.00
[/TD]
[TD="bgcolor: #44546A"]12.00[/TD]
[TD="bgcolor: #44546A"]17.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A18[/TD]
[TD="bgcolor: #44546A"]PLAN[/TD]
[TD="bgcolor: #44546A"]5.00
[/TD]
[TD="bgcolor: #44546A"]8.00[/TD]
[TD="bgcolor: #44546A"]0.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A19[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
In cell C18, I have only one Team criteria:
SUMIFS(INDEX($D$4:$F$9,,MATCH(C$13,$D$3:$F$3,0)),$B$4:$B$9,$D$12,$C$4:$C$9,IF(C$14="","<>",$B$14))
In cell D18, I have two Team criteria, one of which is blank. If I populate the blank team data, the formula does not work:
SUMIFS(INDEX($D$4:$F$9,,MATCH(D$13,$D$3:$F$3,0)),$B$4:$B$9,$D$12,$C$4:$C$9,IF(D$14="","<>",$B$14),$C$4:$C$9,IF(D$15="","<>",$B$15))
And last, in cell E8, I tried having all tree Team criteria, and as expected, it doesn't work either:
SUMIFS(INDEX($D$4:$F$9,,MATCH(E$13,$D$3:$F$3,0)),$B$4:$B$9,$D$12,$C$4:$C$9,IF(E$14="","<>",$B$14),$C$4:$C$9,IF(E$15="","<>",$B$15),$C$4:$C$9,IF(E$16="","<>",$B$16))
Is this something really simple that I'm missing? Thanks in advance.