Sum data based on two things

[doylzer]

Hi All,

I am trying to add sales figures, by where I can change the employees names and weeks past resulting in a total figure of sales for all the previous weeks.

This might make more sense when you see the attached.

I want to change E15 and E16.

If i change the week to 8 it will add all "Paul" data for Week 1-8

Thanks

[Fotis1991]

Perhaps

Please Login or Register  to view this content.

[doylzer]

Thanks for your time and help.
prefect

[TMS]

Edit: Sorry, that should be:

Please Login or Register  to view this content.

Rather than:

Please Login or Register  to view this content.

It needs to add 1 to the week number to get the correct row in INDEX($K:$K,$E$16+1)

Regards


Regards, TMS

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.

[AlKey]

=AVERAGE(IF(A2:A11<=E16,IF(C1:K1=E15,C2:K11)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[TMS]

See the sample attached to compare the solutions.

Regards, TMS

[TMS]

Thanks for the rep