CountIf Query

[tonky16784]

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




--
tonky16784

[joeu2004]

"tonky16784" <tonky1678...@excelbanter.com> 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))