Sumproduct v CF v pivot query

[Andrew 150]

Hi all,

I have searched the forum but struggling with a query.

I have a list of trainees (A) per workplace (B) and a list of the dates their training courses become invalid (C). I have setup conditional formatting to show this.

I would now like to setup a count of the number of trainees with expired training per unit. I have read its best not to count by colours but cant seem to work out how to do this using sumproduct.

Appreciate any assistance.

thanks

 

[Patrick]

I would try looking into the COUNTIF function. Something along the lines of Countif(C, date).

 

[Smallman]

Hi Andrew

Welcome to Chandoo forum

Plenty of ways to do the following. Here is an example with a pivot table.

Take care

Smallman

 

[Hui]

Posting a sample file is the easiest way to get a precise answer

 

[Andrew 150]

Thanks all so far and good idea Hui. So far as I have:
=SUMPRODUCT(--(A6:A18=Q5),--(F6:F18=TODAY()<=-Q6),--(F6:F18>0))

I have posted an example which hopefully will provide a more clear idea of where I am upto and my errors!

Appreciate any assistance please.

Basically I need to calculate the number of trainees from a particular unit who have expired their training and how many are due within a certain date. I seem to be getting there with sumproduct but it seems to be counting the total number of cells, not identifying the out of dates and also counting blanks?!

thanks

Andrew

 

[BBD]

Hi Andrew,

Here you go.

Cheers,
BD

 

[Andrew 150]

Excellent, thank you very much.

 

[BBD]

Glad to help Andrew!!