CountIf Query
 

Morning Everybody

Need some advice on a spreadsheet. At work they are currently manually counting things in other spreadsheets to input the data on this one. Now I've managed to get half of the count if functions working but the second half i'm having trouble with. Im trying to get the spreadsheet to count the number of instances within the month of May and every variant i've tried, it keeps returning 0.

I've tried these two methods:
=COUNTIF('[Recruitment Activity Report.xls]AWE - MANPOWER 2012'!$H$10:$H$1459, "???05???")
=COUNTIF('[Recruitment Activity Report.xls]AWE - MANPOWER 2012'!$H$9:$H$176,"May")

Any help would be gratefully received

Thanks again

CountIf Query
 

"tonky16784" wrote:
Im trying to get the spreadsheet to count the number of
instances within the month of May and every variant i've
tried, it keeps returning 0. I've tried these two methods:
=COUNTIF('[Recruitment Activity Report.xls]AWE - MANPOWER
2012'!$H$10:$H$1459, "???05???")
=COUNTIF('[Recruitment Activity Report.xls]AWE - MANPOWER
2012'!$H$9:$H$176,"May")

You neglect to say what exactly what values are in column H: Excel dates;
or strings perhaps of the form "May 2012".

Assuming you have Excel dates (which might be __formatted__ to look like May
2012), try:

=SUMPRODUCT(--(MONTH('[Recruitment Activity Report.xls]AWE - MANPOWER
2012'!$H$10:$H$1459)=5))

The double-negative converts the logic values TRUE and FALSE into 1 and 0,
which SUMPRODUCT requires. Any arithmetic operation would do the same. So
suppose you want to limit the count to just May 2012 (assuming you have more
than one year of data). You would write:



=SUMPRODUCT((MONTH('[Recruitment Activity Report.xls]AWE - MANPOWER
2012'!$H$10:$H$1459)=5)
* (YEAR('[Recruitment Activity Report.xls]AWE - MANPOWER
2012'!$H$10:$H$1459)=2012))