Excel formula to sum multiple values in a week tied to a person

[MPOH]

I need some help figuring out a formula to sum multiple values in a week tied to a person. At this point, I have solved this with a SUMIF formula, but I'm looking for a more robust/repeatable solution. I'll explain my problem below, as it will make more sense with more context.

Here is a more detailed breakdown:

MainTable (this is where I am trying to display summed values):

Name	16-Mar	23-Mar	30-Mar
Joe	[Summed values for Joe during the week of 16-Mar.]	[Summed values for Joe during the week of 23-Mar.]	[Summed values for Joe during the week of 30-Mar.]
Brad	[Summed values for Brad during the week of 16-Mar.]	[Summed values for Brad during the week of 23-Mar.]	[Summed values for Brad during the week of 30-Mar.]
Sue	[Summed values for Sue during the week of 16-Mar.]	[Summed values for Sue during the week of 23-Mar.]	[Summed values for Sue during the week of 30-Mar.]

LookupTable (this is the source table with the data I am referencing):

Name	Project	16-Mar	23-Mar	30-Mar
Joe	Project A	5	10	5
Joe	Project B	5	5	5
Brad	Project A	20	5	20
Joe	Project C	15	5	15
Sue	Project A	10	25	10
Brad	Project B	5	10	5
Joe	Project D	15	20	15
Brad	Project C	15	25	15
Sue	Project B	30	15	30

Like I said, I've solved this already with a SUMIF formula. For example, this is the formula that would sit in MainTable B2: =SUMIF('LookupTable'!$A:$A, A2,'LookupTable'!$C:$C). That returns a value of 40. I then just copy that formula across each cell in the main table and I get my results for each person, for each week.

My issue is that on the main table, I will hide weeks that are in the past. So the formula works right now, but once we reach March 23rd, I will hide the week starting March 26th. I instead want to create the formula to take into account the actual date in the header row (since I have the date in both tables), but my attempts at figuring that out have proven unsuccessful so far. I have tried this using a SUMIFS formula:

=SUMIFS('LookupTable'!C2:E9, 'LookupTable'!C1:E1, B1, 'LookupTable'!A:A, A2)

But this currently returns an error. I assume I am missing something in my understanding/assumptions, so here is my thinking and understanding of my parameters:

'LookupTable'!C2:E9 ==> this defines the lookup range of values to sum. I have it set to look at all non-name/project/date cells in the lookup table.
'LookupTable'!C1:E1 ==> this defines the first criteria range. This is where I'm looking at the dates.
B1 ==> this defines the first criteria. This should take the value in B1 (16-Mar) and find the matching value in the previous criteria range.
'LookupTable'!A:A ==> this defines the second criteria range. This is where I'm looking at the names.
A2 ==> this defines the second criteria. This should take the value in A2 (Joe) and find rows with matching values in the previous criteria range.

Am I on the right track? I am looking either for feedback to tweak my sumifs formula, if that is on the right track. Or for recommendations if there is a better way to tackle this problem.

 

[Eric W]

Welcome to the MrExcel forum!

How about:

Book1
	A	B	C	D
1	Name	16-Mar	23-Mar	30-Mar
2	Joe	40	40	40
3	Brad	40	40	40
4	Sue	40	40	40
Sheet6

Cell Formulas
Range	Formula
B2:D4	B2	=SUMIF(LookupTable!$A:$A,$A2,INDEX(LookupTable!$C:$Z,0,MATCH(B$1,LookupTable!$C$1:$Z$1,0)))

 

[MPOH]

Welcome to the MrExcel forum!

How about:

Book1
	A	B	C	D
1	Name	16-Mar	23-Mar	30-Mar
2	Joe	40	40	40
3	Brad	40	40	40
4	Sue	40	40	40
Sheet6

Cell Formulas
Range	Formula
B2:D4	B2	=SUMIF(LookupTable!$A:$A,$A2,INDEX(LookupTable!$C:$Z,0,MATCH(B$1,LookupTable!$C$1:$Z$1,0)))

Thanks for the quick response. The concept behind your formula makes sense, but when I apply it to my sheet the result is #N/A (which is better than the #VALUE! result I was getting with my SUMIFS attempt). I am trying to figure out why that is the case. Does the format of the dates matter?

 

[MPOH]

I don't see a way to edit my previous response, but the reason I'm asking about the date format...when I use Excel's evaluator tool to step through the calculations, it is interpreting the "16-Mar" as "43906" within the formula. Everything else appears to be evaluating correctly. If it helps, I have the dates formatted as Dates in an "DD-MMM" format in both tables.

 

[Eric W]

The #N/A! is very likely coming from the MATCH where it's not finding a match. The format/type of the dates is the first place to look. Excel stores dates as a number, starting at Jan 1, 1900, and just formats it to look like a date. So 43906 is how Excel sees the date of 16-Mar, and that's what you'll see in the evaluator tool. Make sure that dates in both tables are saved/displayed the same. If one table is a number 43906 formatted as a date, and the other is a text value "16-Mar", then they won't match. Change the format of both cells to General to see the underlying value. If they are in fact a match, check to see if the ranges in your formula are correct, and that they contain the matching values.

 

[MPOH]

Yeah, the formatting is odd. On the MainTable, 16-Mar gets translated to 43906. On the LookupTable, it gets translated to 3/16/2020. But even the way the formatting is getting applied appears inconsistent:

• On the MainTable, I changed the format to general and typed in 3/16/2020. That automatically changed to 43906. I then changed the format to date, DD-MMM. The value changed from 43906 to 16-Mar.
• On the LookupTable, I changed the format to general and typed in 3/16/2020. That automatically changed to 43906. I then changed the format to date, DD-MMM. But the value remains as 43906.

I imagine this is at the root of the issue, as the auto-formatting seems to be applied differently from one worksheet to the next. Any ideas how to work around that?

 

[MPOH]

I did some more tweaking and found the issue. The data (including the column headers) in the lookup table was formatted as a table. This is what caused the funkiness with the date format applying correctly. Once I removed the table formatting, I was able to re-format the date field to match the date format on the main table, and the formula worked. Thanks for your help!

 

[Eric W]

Glad you got it figured out!