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

#### MPOH

##### New Member
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.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

Book1
ABCD
1Name16-Mar23-Mar30-Mar
2Joe404040
4Sue404040
Sheet6
Cell Formulas
RangeFormula
B2:D4B2=SUMIF(LookupTable!\$A:\$A,\$A2,INDEX(LookupTable!\$C:\$Z,0,MATCH(B\$1,LookupTable!\$C\$1:\$Z\$1,0)))

#### MPOH

##### New Member
Welcome to the MrExcel forum!

Book1
ABCD
1Name16-Mar23-Mar30-Mar
2Joe404040
4Sue404040
Sheet6
Cell Formulas
RangeFormula
B2:D4B2=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

##### New Member
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

##### MrExcel MVP

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

##### New Member
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

##### New Member
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

##### MrExcel MVP
Glad you got it figured out!

Replies
2
Views
144
Replies
8
Views
161
Replies
1
Views
49
Replies
3
Views
211
Replies
6
Views
182

1,127,105
Messages
5,622,763
Members
415,926
Latest member
jerrynababa

### 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.

### Which adblocker are you using?

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

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