# Excel - CountIf Query

Asked By tonky16784 on 22-May-12 04:47 AM
```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 have 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 have 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 replied to tonky16784 on 22-May-12 11:13 AM
```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))```