using countif function to track courses and expiry date

[tangle88]

Hi everyone!

I am trying to use the countif function to consolidate all employees who completed specific training and with expiry date 3 and 6 months from the current date.
WORKER'S TRAINING.xlsx

Thank you.

[shukla.ankur281190]

Hi,

Welcome to excel forum.

Please explain more or put expected result in your attached sheet.

Thanks
Ankur

[Glenn Kennedy]

Is this what you wanted?

[berlan]

Hi,

see if the attached can be of help. Comments in red and formula changes highlighted in green.

Cheers,
berlan

[XOR LX]

@Glenn Kennnedy
@berlan

Sorry for the off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in-thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do so at all.

Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Regards

[Glenn Kennedy]

XOR LX. You have made this comment to me once before, I think, in another thread. I replied and, in addition, I made some comments about my views in a thread that you started (Post 48)
http://www.excelforum.com/the-water-...ed-file-4.html

I still maintain that "horses for courses applies and that formulae like
=COUNTIFS($B$11:$B$13,A2,$D$11:$D$13,1,$F$11:$F$13,">"&TODAY())

or this:
=COUNTIFS($B$11:$B$13,A2,$D$11:$D$13,1,$F$11:$F$13,">"&DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())))

will mean nothing, if not seen in the context of the data layout. The OP gave no verbal picture of the data layout, so I had nothing to build on.

[XOR LX]

@Glenn

Agreed that context is important, but not sure what you mean re "the OP gave no verbal picture of the data layout". The OP provided a workbook, to which your solution appears to apply.

The point is that, as well as providing a workbook (as you did), I think it benefits all if you also post your formulas in the actual thread.

The thread title in this case: "using countif function to track courses and expiry date" is one which might well be picked up by searches relating to the topic here, and your formulas, although specific to the OP's request, are nevertheless of a sufficiently generic form such that any readers stumbling upon this thread might learn something of the functioning of COUNTIFS by studying the syntax of those formulas, and that without necessarily having to download any attachments.

Regards

[tangle88]

Hi guys,

Thanks for all your reply. I actually didn't expect a reply so soon moreover so many replies.
I think i did not do a good job explaining the end result i wanted.
Basically, what i wanted is:
1. the top section being able to capture the total amount of people trained in a course (eg. confined space, work at height)
2. the top section capturing the amount of people whose certification will be not be expiring in 6 months or less time as entered in the bottom section expiry dates.
3. the top section capturing the amount of people whose certification will be expiring in 3 months or less time as entered in the bottom section expiry dates.

so its like counting the amount of people for a specific training course with variables taken from column B11 to B13, D11 to D13 and F11 to F13 for type of training, number of personnel and expiry dates respectively.

Thank you for all your input!

[Glenn Kennedy]

OK. is this it? If not, please add your expected results manually and repost.

[Jacc]

Something like this?

[tangle88]

OK. is this it? If not, please add your expected results manually and repost.

That's what i'm looking for!

Thanks for your help!

And also thanks for all the replies!