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!!

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!!!

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?