Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif query | Excel Worksheet Functions | |||
COUNTIF function query | Excel Worksheet Functions | |||
COUNTIF Query | Excel Programming | |||
COUNTIF Query | Excel Programming | |||
Countif Query | Excel Discussion (Misc queries) |