Array Arithmetic, Excel, Non-VBA

Question

I am not looking for a VBA solution to this. The goal is to find how many hours John has worked in a 12 month period, starting on 7/1/2015. I have two worksheets. Worksheet one looks like the following:

Name   |     Date Start     | Total Hours
John   |     7/1/2015       |    Formula goes here

Worksheet two

   Date    |     John           |    Jane         | Joe
  7/1/2015 |      3             |    2            | 2.5
  7/2/2015 |      3             |    2.5          |  0
  rest of attendance data

The dates can go down as far as the spreadsheet will allow. What I'm looking to do is to keep the function in the worksheet formula. The formula I have now is:

=SUMIFS(Worksheet2!B:B, Worksheet2!$A:$A, ">="&Worksheet1!$B2, Worksheet2!$A:$A, "<="&EDATE(Worksheet1!$B2, 12))

If I did this through VBA, I could do something like:

Range("B"&2 & ":" & "B" & 1058476)

But I am looking for some way to look up what column the person, whos name is on sheet 1 in the row, what column their name occurs in sheet 2; then, sum up all the values in that column in sheet 2 that fall between the the 12 month time span, and put that number in worksheet 1 cell C2. I don't want to use Worksheet2!B:B.

Nevermind! I have answered this - something like: =SUMPRODUCT(OFFSET(Attendance!$A$1, 0, 1, 368)) — bdpolinsky
– bdpolinsky, Commented Apr 13, 2017 at 20:47

WNG · Accepted Answer · 2017-04-13 20:46:16Z

1

try to use Offset.

=SUMIFS(OFFSET(Worksheet2!A:A,0,MATCH(Worksheet1!$B1,Worksheet2!$A2:$A100,0)), Worksheet2!$A:$A, ">="&Worksheet1!$B2, Worksheet2!$A:$A, "<="&EDATE(Worksheet1!$B2, 12))

should fetch the correct column.

answered Apr 13, 2017 at 20:46

WNG

3,8153 gold badges25 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Array Arithmetic, Excel, Non-VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related