Question re count option in Excel

  oo7juk 00:03 08 Aug 07
Locked

Hi,

I have a workbook that contains 2 sheets. The first sheet is my results page and contains vlookup formulas the second sheet is where my data is stored.
On my first sheet I have 3 columns one is headed 'type' the next is headed 'complete' and the last one is headed 'refresher due?'.
The vlookup refers to my data sheet and populates my 2nd column which in turn triggers my 3rd column if applicable.

Under type I have various types of traing listed as text. I have used 'countblank to determine the number of training courses that have not been completed and used vlookup to populate a cell within sheet 1.

If the 2nd column is populated with a date and triggers a refresher date some times the refresher date has expired e.g. completed 01.03.06 refresher due 01.03.07. I have used conditional formatting to highlight cell, but is there anyway I could count the number of cells that are expired. I was thinking of creating additional columns in my 'data' sheet and referencing that way, but only way I think that would work would be to highlight the cells and maybe count them that way.

I also have training that is due to expire ( 3 months) for employees would like to count that too.

My 1st sheet 'results' has a list of legends indicating what the coloured formatting refers too.


Thank you.

  VoG II 09:39 08 Aug 07

Something like this perhaps:

=COUNTIF(A:A,"<"&DATEVALUE("08/08/07"))

  oo7juk 16:58 08 Aug 07

VoG,

I put your formula into my sheet-

=COUNTIF(B2:B142,"<"&DATEVALUE("08/08/2007"))

The above has counted all cells with a date prior to todays date. As I stated I have used the countblank formula to establish training that hasn't been completed. If the cell has a date then the training has been completed, but may have expired. I'm trying to find a way of counting the expired too and maybe adding onto the non-completed total.

Thanks.

  VoG II 17:03 08 Aug 07

"The above has counted all cells with a date prior to todays date."

Yes, well it should. It was just an example. You would need to replace "08/08/2007" with the date you are interested in or a cell reference (without quotes).

  oo7juk 21:42 08 Aug 07

Unfortunately, the dates will differ from each other so I can't define a certain date.
Would the best option be to insert additional columns next to dates within my data sheet and use conditional formatting to highlight the dates when they expire. If I done that I would then need to count the highlight cells and add them to my total.

Thanks

  VoG II 22:00 08 Aug 07

I'm a bit confused here - can't you use the conditions as in your conditional format.

  oo7juk 23:37 08 Aug 07

confusing myself..........sorry

At the moment I have populated 5 cells with the count of training (type) not completed. The only way I think I can count the expired training is to count the highlighted cells then add to the 'coutblank (not completed)' formula.

e.g. col a has 50 blank cells, the next col has the refresher dates 10 of them are expired. Want to be able to populate a cell on the first sheet with the total outstanding reading 60 instead of 50.

Thanks.

  oo7juk 14:01 10 Aug 07

Hi, don't think I have explained this correctly.

e.g....

Column a titled completed (range a2:a142
Column b titled refresher due (range b2:b142)

if cell 'a2' is populated with this date (01/06/07) it will populate cell 'b2' with this date (01/09/07) Already have a formula in place to calculate refresher date.

I want to populate a cell within the sheet that will tell me how many dates are due to expire within 3 months based on the current date of the sheet opening.

I tried using this formula, but can't get it to work-

=COUNTIF(range,">="&TODAY())-countif(range,">="(TODAY(),3))

thanks.

  VoG II 15:10 10 Aug 07

I'm still a little confused.

I have created a list of dates in column A. Using column B as a helper column, in B1 I have used

=A1-TODAY()

and copied down. Column B needs to be formatted as number.

In another cell I have

=COUNTIF(B:B,"<=90")

which correctly counts the number of dates in A that are less than 3 months away (give or take a day or two).

  oo7juk 10:25 13 Aug 07

Thanks for the above. Done what you said but, it seems to be counting all the negative numbers. Was only wanting it to count if cell is populated with 0-89 days left, hope that makes sense.

  VoG II 10:32 13 Aug 07

Try this:

=SUMPRODUCT(--(B1:B1000>0),--(B1:B1000<=90))

please note, the -- are consecutive minus signs.

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Gear VR vs. Daydream: Which delivers the best VR experience?

Best Mac antivirus 2017