Interesting Problem

[Jtucker10278]

I have been fighting with SUMIFS for the last few days and finnaly figured out WHAT was wrong but I am hoping someone can tell me WHY


When i put together the table below I typed "Week_01" into cell M6 then I grabbed the little corner doohickey and dragged to the right

This Auto enters Week_02 Week_03 Week_04

then I use a SUMIFS formula

=SUMIFS(PNLQTY,PNLWEEK,M$6,PNLEMPLOYEE,$L7)


but when my sumifs formula references cell N6 that was "autocomplete" entered the formula does not work

but if hand type "Week_02" into cell N6 then my formula works


?????????????????????????

	L	M	N	O	P
Week _01	Week_02	Week_03	Week_04
Amanda Holmes
Chuck Reynolds
Cody Harnish
Mark Lopez

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]

[TD="align: center"]8[/TD]

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

[TD="align: center"]9[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]

</tbody>

PNL_Numbers​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M7[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,M$6,PNLEMPLOYEE,$L7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N7[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,N$6,PNLEMPLOYEE,$L7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O7[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,O$6,PNLEMPLOYEE,$L7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P7[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,P$6,PNLEMPLOYEE,$L7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M8[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,M$6,PNLEMPLOYEE,$L8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N8[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,N$6,PNLEMPLOYEE,$L8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O8[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,O$6,PNLEMPLOYEE,$L8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P8[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,P$6,PNLEMPLOYEE,$L8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M9[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,M$6,PNLEMPLOYEE,$L9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N9[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,N$6,PNLEMPLOYEE,$L9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O9[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,O$6,PNLEMPLOYEE,$L9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P9[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,P$6,PNLEMPLOYEE,$L9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M10[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,M$6,PNLEMPLOYEE,$L10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N10[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,N$6,PNLEMPLOYEE,$L10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O10[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,O$6,PNLEMPLOYEE,$L10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P10[/TH]
[TD="align: left"]=SUMIFS(PNLQTY,PNLWEEK,P$6,PNLEMPLOYEE,$L10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]PNLEMPLOYEE[/TH]
[TD="align: left"]=PNL_Numbers!$E$5:$E$1048576[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]PNLQTY[/TH]
[TD="align: left"]=PNL_Numbers!$H$5:$H$1048576[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]PNLWEEK[/TH]
[TD="align: left"]=PNL_Numbers!$C$5:$C$1048576[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

It might help if you showed us the data in your named ranges as what you have are referring to are columns C,E and H and they aren't in the table you have posted.

 

[Jtucker10278]

UPDATE!!!!!!!!

SO yea ..... I had an arrant space as in Week _01

I must have read 5 different forum threads that all said "make sure you don't have any arrant spaces or your formula wont work"

there are no EMOJI's of a guy stabbing himself in the eye with a fork but I feel like that would be appropriate here.

 

[MARK858]

Silly question but the your question implied that the SUMIFS worked for M6... how as surely you were dragging the formula as well?

BTW, you could use the smiley

 

[Jtucker10278]

thank you for the head banging smiley, i think i will just make that my signature!

So the whole setup is this

I have employees

employees build panels

I want to total how many panels are built in a week

But a WEEK is defined as Sat through Friday

So in my first table i list all 365 days and next to that I manually enter what Week number each day belongs to


1/1/18 Week _01 but i type out Week _01 with extra space and copy it down 6 cells
1/2/18 Week _01
~~~~
~~~~
~~~~
1/6/18 Week_02 now i type Week_02 no extra space and copy it down 6 cells
1/7/18 Week_02

then in my daily input table I just type the date, employee name and panel qty and it looks up and enters the week number based on my first table that had the extra space but only for week _01

Now this is where the 3 days of insanity start


when i type Week_01 No extra space my first SUMIFS formula wont work

so i get mad and copy-paste value the cell that has Week _01 with the extra space

now my first formula works but when i drag the week num to the right it adds an extra space to all the week num

seriously since Tuesday I have been getting up early to work on this project and getting nowhere.

The moral of the story is dont do spreadsheets at 3am

 

[MARK858]

Ah, I understand now and to think how much sleep =LEN(M6) copied across would have saved