Solved

# Excel Sumif(s) using networkdays?

Posted on 2014-10-07
316 Views
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
Question by:swjtx99

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 40365851
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))"
``````
0

Author Closing Comment

ID: 40366170
Perfect.

0

## Featured Post

### Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…