Sum Values in Row Between Date Range If Match Criteria?

[Jtucker10278]

I don't know what happened but i tried to post this and its gone, so here it is again


I have a table (does not have to be a "table'") with names down the left and all 365 dates across the top

	1/1	1/2	1/3	1/4	1/5	1/6	1/7
Adam	3	4	5	2	3	3	4
John	1	4	3	4	2	3	5
Dave	6	4	2	5	3	4	6
Joe	3	4	2	1	1	5	6

<tbody>
</tbody>

I am trying to come up with a formula that will sum that values for adam for dates between x and y (also looking to this with average and count)



The end result would be another table/spreadsheet with each employees totals and average for each week

	Week 1	Week 1	Week 2	Week 2
	Total	Average	Total	Average
Adam	23	4.6	18	3.8
John	17	3.5	15	3
Dave	19	3.8	11	2.01
Joe	6	1.1	8	1.4

<tbody>
</tbody>

I'm always working with week totals so the date range will always be 7 days, or if possible using weeknum to calculate.

Also I can rotate the headings with names across the top and dates down the side if that will make it work

If no formula like this exists I am open to suggestions on other ways to tackle the problem. I'm pretty decent with excel so any help or thoughts wouls be apreciated

 

[jorismoerings]

Hi,

Take a look at this and see if this helps:

Book1
	A	B	C
1		Week 1	Week 1
2		Total	Average
3	Adam	24	3,4
4	John	22	3,1
5	Dave	30	4,3
6	Joe	22	3,1
Sheet2

Cell Formulas
Range	Formula
B3	=SUMPRODUCT((WEEKNUM(Sheet1!$B$1:$CN$1+0)=VALUE(RIGHT(B$1,2)))*(Sheet1!$A$2:$A$5=Sheet2!$A3)*(Sheet1!$B$2:$CN$5))
C3	=B3/SUMPRODUCT((WEEKNUM(Sheet1!$B$1:$CN$1+0,1)=VALUE(RIGHT(B$1,2)))*(Sheet1!$A$2:$A$5=Sheet2!$A3)*(Sheet1!$B$2:$CN$5<>""))

 

[Jtucker10278]

Unfortunately that keeps coming back as 0

I did some looking this morning and found sum intersection formulas that are kind of working.

For example if i put all the columns in week 1 in a named range "Week1" and then make row 3 a named range "adam"

the formula is =sum(Week1 adam)

and that works now i just need to find a way to write the formula so that instead of using named ranges i could Point to the named range. if that makes sense?

thanks for any thoughts

 

[Jtucker10278]

GOT IT!

=SUM(INDIRECT(B12) INDIRECT(A16))

Cell B12 contains text "week1"
Cell A16 contains text "adam"

sometimes i forget how absolutly amazing Excel is, and how much I love it.

Thanks for your help getting me there.