Search results

Hi there, Try this =SUMPRODUCT(--(MOD(A1:A26,2)=0),A1:A26) Regards Sara

Thank you so much.

Try this; =COUNTIFS(Sheet3!$A$2:$A$500,"EEE",Sheet3!$B$2:$B$500,">=07/01/2013",Sheet3!$B$2:$B$500,"<=07/31/2013") If you need to be able to alter the dates, it would pay to put them in a cell, say A1 and A2. If you do, you'll need to alter the last part...

Hi Narayan That's brilliant. Can you explain how the formula in K1 works? =MIN(IF(WEEKDAY(ROW(INDIRECT(""&DATE($J$1,MONTH($B$1&0),1)&":"&DATE($J$1,MONTH($B$1&0),8))),2)<=5,ROW(INDIRECT(""&DATE($J$1,MONTH($B$1&0),1)&":"&DATE($J$1,MONTH($B$1&0),8))))) Cheers Sara

Try this http://www.youtube.com/watch?v=SCiqQiC7Bwk

Hi Narayan It is the 1st pay week of 2013 Regards Sara

Hi Narayan To clarify, point 1 - spot on! Point 2 - The payweeks are never ending. The financial year is separated into months of the year for my accountants starting with July. The accountants only want to know about working days within their months, split into weeks. June 2013: Pay...

Many apologies - it had been marked with "Solved"

Hi Narayan The pay weeks move with the financial year (Jul - Jun). The purpose behind the dates is to match the relevant pay week data with the account week per month. The accounting week will divide the pay period into the selected month and count the number of networking days. For...

Hi there I've gotten stuck again...http://chandoo.org/forums/topic/automate-accounting-weeks File link: https://skydrive.live.com/redir?resid=9D4A15C8F236789E!118&authkey=!AKYmR0rtugSHAgQ Based on a Month/Year selected, I need to break into the Pay Weeks (Sun - Sat) and Accounting weeks...

Hi Smallman I've gotten stuck again. File link: https://skydrive.live.com/redir?resid=9D4A15C8F236789E!118&authkey=!AKYmR0rtugSHAgQ Based on a Month/Year selected, I need to break into the Pay Weeks (Sun - Sat) and Accounting weeks (Mon - Fri) The accounting weeks must start and end...

Thanks Smallman for getting me started. I amended it slightly so that if the 1st of the month was a Sunday the date didn't change =IF(WEEKDAY(B5)=1,B5,B5-WEEKDAY(B5,2)) Although I hadn't stated, the accounting dates needed to be within the month =IF(MONTH(B11+1)<>MONTH(B5),B5,B11+1)...

Hi there I'm getting lost...help please. I am trying to set up the beginnings of a dashboard taking payroll data (Sunday to Saturday) and adjusting to accounting weeks (Monday to Friday). The accounting weeks vary from 4-5 each month, and the payroll data needs to be adjusted often for...

You lot are geniuses! SirJB7 - the IT department are speeding up my pc...or replacing it Narayan - Cheers - there's so much more to understand with array formulas Haseeb - Changing to your formulas, it now calc's in seconds Thanks to you, I can change a fair few of my other workbooks too.

Hi there Here is the link to the file https://skydrive.live.com/redir?resid=9D4A15C8F236789E!112&authkey=!AA4elyD7lzSI9c4

Hi Hui! Thanks for that, unfortunately I'm getting #N/A as a result. =ROUND(SUMPRODUCT((Dept_Id=B2)*(Line_Descr={"Earnings","N*","R*"})*(Account={800000,800500,800100,800215})*Units),2) Would I be better off using helper columns in a data table? Regards Sara

Hi SirJB7, My ranges are not dynamic. All ranges are fixed and equal lengths. Account: =Details!$F$4:$F$3115 Acct_Desc: =Details!$G$4:$G$3115 Amount: =Details!$L$4:$L$3115 Dept_Id: =Details!$D$4:$D$3115 Emplid: =Details!$A$4:$A$3115 Ignore_Me: =Details!$O$4:$O$3115 Jobno...

Hi SirJB7, There are 15 ranges in all, each is usually around 3500 rows. There are no other computers available to test on. Looking at the formula I can see that they are repetitive, but I can't figure out how to simplify or combine the "or" portions. Regards Sara

Hi there I have a really slow workbook that has 88 sumifs formulas plus 22 array formulas. While I was pretty pleased that I'd figured out how to write them, I'm pretty sure that there must be a way of rewriting them to make them faster. This is a weekly task for me and currently the...

Thank you all for your help. Using Sajan's instructions I have made everything work the way I imagined it. From here, I think I'll start trying to figure out why it works and research the array formulas and upskill Cheers again

Thank you so much for an exceptionally fast response! I'll spend the rest of today (kiwi time) working my way through your solutions and I'll let you know how I get on.

Here is the link to the file https://skydrive.live.com/redir?resid=9D4A15C8F236789E!107&authkey=!ABaNsWjpfjZQOpY

Hi Wonderful Excel Experts I've created an excel table showing weekly financial data that can then be pulled into a dashboard. Each week, 3 more rows are added, 1 for each area with the same effective date. Period End Dt / Area / Calc / Year / Month 29/12/12 / Area A / XXXX / 2012 / Dec...

Hello everyone, I find myself searching this site on a daily basis to help out in my job as a payroll analyst. There's so much that I don't know, and exciting when I find something new to add to my tool box of tricks.