counting aid

[shorie7]

Hello. I need to be able to count the last 12 full weeks worked. This may not be the last 12 weeks, as I may of had a day off sick in the last twelve. I need to work out my last 12 weeks average pay for holiday accrual. I'm not really making sense here I guess, but I know what I'm trying to do, I just can't do it. Basically I need excel to find the last 12 times I worked over 37.99 hours, add them up to give me a total, and then stop. Any advice please? I can't upload my worksheet, as it's too big. I'm a complete newbie, and proud of my efforts so far, so please be kind and patient. Thanks in advance. Mark

[JohnTopley]

Please post a sample of your data showing expected results.

[shorie7]

Thanks John for your reply. I have uploaded a copy of some of my spreadsheet.
On page one are my wages from September,
including days off or holiday.
On page two, are the last 12 FULL weeks I have worked.
This sheet was done by copying all the rows with a RED CELL in them,
that shows I worked 38 hours, and then PASTED onto sheet two.
This is what I want excel to do for me automatically. Find the last 12 FULL weeks(38 hours plus),
and add up the total earnings from them.
Any advice you can give would be helpful. I have been looking up the COUNTIF/COUNTIFS, but think these will just
count the totals altogether, and not filter out the 12 full weeks, and stop at the last 12.
Hope I'm making sense here.
Thanks again
Mark

[shorie7]

Try again, files attached

[shorie7]

Third time lucky.... attached worksheet

[FDibbins]

You have 2 week 46's in your table...rows 33 and 51 (looks like row 33 should be 28?)

If so, then put this ARRAY formula in A6 and copy down...
=IF(ROWS($A$6:A6)>12,"",LARGE(IF('This is my Earnings'!$D$6:$D$57>=38,'This is my Earnings'!$A$6:$A$57),ROW(A1)))
(if you want to have the "12" easily adjustable, put that in it's own cell, and reference it instead)
...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.

Then for the other data...
1. make sure your headings on each sheet match - in the FIRST row only.
2. Copy thos to B6, then copy down and across as needed...
=IF(A23="","",INDEX('This is my Earnings'!$A:$N,MATCH('This is the last 12 FULL WEEKS'!$A23,'This is my Earnings'!$A:$A,0),MATCH('This is the last 12 FULL WEEKS'!B$1,'This is my Earnings'!$A$1:$N$1,0)))
You need to copy the formatting across, so they look how you want them to

[JohnTopley]

See attached. Formulae in A to C of the copy worksheet.

I adjusted Ford's formula (column B) to get the dates in order required. The formula in A & C can be used to retrieve other data from your "Earning" sheet: just modify the range in the INDEX but leave MATCH as it is.


Also changed your summation/averages to be formulae.

Note : change Match to $Bn rather than Bn


=INDEX('This is my Earnings'!$C$6:$C$57,MATCH($B6,'This is my Earnings'!$B$6:$B$57,0))

Hope this helps

[samba_ravi]

See the attached file
i do it for 2 columns for you rest do your own