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

MPOH

New Member
Joined
Mar 9, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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):
Name16-Mar23-Mar30-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):
NameProject16-Mar23-Mar30-Mar
JoeProject A5105
JoeProject B555
BradProject A20520
JoeProject C15515
SueProject A102510
BradProject B5105
JoeProject D152015
BradProject C152515
SueProject B301530

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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
Welcome to the MrExcel forum!

How about:

Book1
ABCD
1Name16-Mar23-Mar30-Mar
2Joe404040
3Brad404040
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
Joined
Mar 9, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel forum!

How about:

Book1
ABCD
1Name16-Mar23-Mar30-Mar
2Joe404040
3Brad404040
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
Joined
Mar 9, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,640

ADVERTISEMENT

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
Joined
Mar 9, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 9, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

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
Top