Solved

Excel Sumif(s) using networkdays?

Posted on 2014-10-07
2
363 Views
Last Modified: 2014-10-07
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
Comment
Question by:swjtx99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
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))"

Open in new window

0
 

Author Closing Comment

by:swjtx99
ID: 40366170
Perfect.

Thanks for your help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question