Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Excel Sumif(s) using networkdays?

Hi,

I have the following two formulas that are inserted into cells. on sheet2. The first sums the values in column K of every row where column F is a date that is 10 days prior to today. The second sums the values in column K of every row in column F where the date is between 11 and 20 days prior to today. Unfortunately, instead of "calendar" days prior to today, I actually need "networkdays" prior to today. How would I modify these formulas to get that result?

   Range("M20").Formula = "=SUMIF('Sheet1'!F:F,""=""&TODAY()-10,'Sheet1'!K:K)"
   Range("M21").Formula = "=SUMIFS('Sheet1'!K:K,'Sheet1'!F:F,""<""&TODAY()-10,'Sheet1'!F:F,"">""&TODAY()-20)"

Thanks,

swjtx99
0
swjtx99
Asked:
swjtx99
1 Solution
 
Rory ArchibaldCommented:
Do you mean:
Range("M20").Formula = "=SUMIF('Sheet1'!F:F,""=""&WORKDAY(TODAY(),-10),'Sheet1'!K:K)"
   Range("M21").Formula = "=SUMIFS('Sheet1'!K:K,'Sheet1'!F:F,""<""&WORKDAY(TODAY(),-10),'Sheet1'!F:F,"">""&WORKDAY(TODAY(),-20))"

Open in new window

0
 
swjtx99Author Commented:
Perfect.

Thanks for your help.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now