Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 10th 15, 04:08 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 4
Default Can anyone help with this formula?

Ok to make a long story short I downloaded this Gage Tracking excel work book. Of course because I have office 2003 I had all kinds of formula issues when I opened the 2007 file. For some reason it has macros which I do not know how to turn off so I disable them as it would like to link to a file that is not there. Fine I will choose disable not a big deal. I was finally able to link everything together otherwise except the first overall view page. Basically this is supposed to list all my gages that are over due for calibrations, how many are due for calibration in 30 days, 60 days, and 90 days. The equipment list (on the InstrumentLog tab) lists out all of the gages, whether or not they are In Service, the last day they were calibrated, and the next due date. First I had #NAME errors then I changed the _xlfCOUNTIFS to SUMPRODUCT and now I am getting #VALUE errors. Below is the formula for both Overdue and Due in 30 Days:

=SUMPRODUCT(InstrumentLog!F2:F47,"In Service",InstrumentLog!K2:K47,"<"&TODAY())

=SUMPRODUCT(InstrumentLog!F2:F47,"In Service",InstrumentLog!K2:K47,""&TODAY(),Instrume ntLog!K2:K47,"<="&TODAY()+30)

InstrumentLog is the sheet that lists out all of the gages and what I said above. F2:F47 is the column that says whether or not the gage is In Service or Out of Service and K2:K47 lists the Next Calibration Due Dates.


Any help anyone could give would be greatly appreciated!!

  #2   Report Post  
Old June 10th 15, 05:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default Can anyone help with this formula?

Hi Dawn,

Am Wed, 10 Jun 2015 16:08:06 +0100 schrieb dawn48313:

=SUMPRODUCT(InstrumentLog!F2:F47,"In
Service",InstrumentLog!K2:K47,"<"&TODAY())

=SUMPRODUCT(InstrumentLog!F2:F47,"In
Service",InstrumentLog!K2:K47,""&TODAY(),Instrume ntLog!K2:K47,"<="&TODAY()+30)


try:
=SUMPRODUCT(--(InstrumentLog!F2:F47="In Service"),--(InstrumentLog!K2:K47<TODAY()))
and
=SUMPRODUCT(--(InstrumentLog!F2:F47="In Service"),--(InstrumentLog!K2:K47TODAY()),--(InstrumentLog!K2:K47<=TODAY()+30))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Old June 10th 15, 06:32 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 4
Talking

Quote:
Originally Posted by Claus Busch View Post
Hi Dawn,

Am Wed, 10 Jun 2015 16:08:06 +0100 schrieb dawn48313:

=SUMPRODUCT(InstrumentLog!F2:F47,"In
Service",InstrumentLog!K2:K47,"<"&TODAY())

=SUMPRODUCT(InstrumentLog!F2:F47,"In
Service",InstrumentLog!K2:K47,""&TODAY(),Instrume ntLog!K2:K47,"<="&TODAY()+30)


try:
=SUMPRODUCT(--(InstrumentLog!F2:F47="In Service"),--(InstrumentLog!K2:K47<TODAY()))
and
=SUMPRODUCT(--(InstrumentLog!F2:F47="In Service"),--(InstrumentLog!K2:K47TODAY()),--(InstrumentLog!K2:K47<=TODAY()+30))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
IT WORKED!!!! You are the best!!!! Thank you!!!
  #4   Report Post  
Old June 10th 15, 06:39 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 4
Default

Quote:
Originally Posted by Claus Busch View Post
Hi Dawn,

Am Wed, 10 Jun 2015 16:08:06 +0100 schrieb dawn48313:

=SUMPRODUCT(InstrumentLog!F2:F47,"In
Service",InstrumentLog!K2:K47,"<"&TODAY())

=SUMPRODUCT(InstrumentLog!F2:F47,"In
Service",InstrumentLog!K2:K47,""&TODAY(),Instrume ntLog!K2:K47,"<="&TODAY()+30)


try:
=SUMPRODUCT(--(InstrumentLog!F2:F47="In Service"),--(InstrumentLog!K2:K47<TODAY()))
and
=SUMPRODUCT(--(InstrumentLog!F2:F47="In Service"),--(InstrumentLog!K2:K47TODAY()),--(InstrumentLog!K2:K47<=TODAY()+30))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Aww crap, one more thing please. Sorry. I just noticed that it says 31-60 days, and 61-90 days. How do I calculate it so that it shows that 6 need to be calibrated in 32 days but not show up in the 31 to 60 days?
  #5   Report Post  
Old June 10th 15, 06:47 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 4
Default

Quote:
Originally Posted by dawn48313 View Post
Aww crap, one more thing please. Sorry. I just noticed that it says 31-60 days, and 61-90 days. How do I calculate it so that it shows that 6 need to be calibrated in 32 days but not show up in the 31 to 60 days?
It will not let me edit my typo. I meant how do I calculate it so that it shows that 6 need to be calibrated in 32 days and shows in 31-60 days but not show up in the 61 to 90 days?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Build formula using field values as text in the formula referencing another workbook solardirect Links and Linking in Excel 6 June 4th 12 10:47 PM
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula PJ[_3_] Excel Worksheet Functions 2 June 2nd 10 03:45 PM
Formula expected end of statement error, typing formula into cell as part of VBA macro [email protected] Excel Programming 1 July 20th 06 07:58 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 03:13 PM
Commenting custom formula fields/formula on formula editor Muxer Excel Programming 2 July 24th 03 01:02 AM


All times are GMT +1. The time now is 04:59 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017