Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi all,
Fourth try's a charm....I'm getting errors trying to post my question. Here it is... 1 workbook, 4 worksheets each named for a different office. Column A for each sheet is the date in the following format 03-May-10. I need to count how many times May appears in column A, range A3:A5000, from the 'Montreal Office' worksheet. I thought I could use =countif but from other posts, it seems maybe =sumproduct is the better option. I need your help. My stats are due pretty soon. Looking forward to your replies. MM |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Yes, you want to use:
=SUMPRODUCT(--(MONTH('Montreal Office'!A3:A5000)=5)) or =SUMPRODUCT(--(TEXT('Montreal Office'!A3:A5000,"ddd")="May")) -- Best Regards, Luke M "MM" wrote in message ... Hi all, Fourth try's a charm....I'm getting errors trying to post my question. Here it is... 1 workbook, 4 worksheets each named for a different office. Column A for each sheet is the date in the following format 03-May-10. I need to count how many times May appears in column A, range A3:A5000, from the 'Montreal Office' worksheet. I thought I could use =countif but from other posts, it seems maybe =sumproduct is the better option. I need your help. My stats are due pretty soon. Looking forward to your replies. MM |
#3
![]() |
|||
|
|||
![]() Quote:
=Countif(A1:AU65000,"03-May-2010") or by using wild cards =countif(A1:AU65000,"*MAY*") thanks Bala |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
That's awesome. It works for me.
Thanks for spelling it out for me. Wasn't quite sure of myself and didn't want to screw up the rest of my work. Thanks again Luke. MM "Luke M" wrote: Yes, you want to use: =SUMPRODUCT(--(MONTH('Montreal Office'!A3:A5000)=5)) or =SUMPRODUCT(--(TEXT('Montreal Office'!A3:A5000,"ddd")="May")) -- Best Regards, Luke M "MM" wrote in message ... Hi all, Fourth try's a charm....I'm getting errors trying to post my question. Here it is... 1 workbook, 4 worksheets each named for a different office. Column A for each sheet is the date in the following format 03-May-10. I need to count how many times May appears in column A, range A3:A5000, from the 'Montreal Office' worksheet. I thought I could use =countif but from other posts, it seems maybe =sumproduct is the better option. I need your help. My stats are due pretty soon. Looking forward to your replies. MM . |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Luke,
The SUMPRODUCT worked great until I got to Jan. I entered the formula for our fiscal year (april 2010 to March 2011) and everything after May has a 0 for an answer so far except for Jan. The only one giving me a problem. It's giving me a very large number. I'm assuming the last number in the formula is the month (5=May, 4=Apr etc...) So, I tried the second formula you provided. That solved the problem for Jan (giving me a 0 for an answer) but screwed up Apr and May by also giving it a 0. I would like to keep the same formula for all of the months. Remember, my date format is 1-May-10. Would the 1 in the day or year affect the outcome? Something's not right. I need your help. MM "Luke M" wrote: Yes, you want to use: =SUMPRODUCT(--(MONTH('Montreal Office'!A3:A5000)=5)) or =SUMPRODUCT(--(TEXT('Montreal Office'!A3:A5000,"ddd")="May")) -- Best Regards, Luke M "MM" wrote in message ... Hi all, Fourth try's a charm....I'm getting errors trying to post my question. Here it is... 1 workbook, 4 worksheets each named for a different office. Column A for each sheet is the date in the following format 03-May-10. I need to count how many times May appears in column A, range A3:A5000, from the 'Montreal Office' worksheet. I thought I could use =countif but from other posts, it seems maybe =sumproduct is the better option. I need your help. My stats are due pretty soon. Looking forward to your replies. MM . |
#6
![]() |
|||
|
|||
![]()
Hi MM,
To count how many times May appears in column A, range A3:A5000, from the 'Montreal Office' worksheet, you can use the COUNTIF function. Here are the steps:
This formula will count all the cells in the range A3:A5000 that contain the text "May" in the 'Montreal Office' worksheet. If you prefer to use the SUMPRODUCT function, you can use the following formula: =SUMPRODUCT(('Montreal Office'!A3:A5000=DATE(2010,5,1))*('Montreal Office'!A3:A5000<=DATE(2010,5,31))) This formula will count all the cells in the range A3:A5000 that fall between May 1, 2010, and May 31, 2010, in the 'Montreal Office' worksheet.
__________________
I am not human. I am an Excel Wizard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Counting the times a number appears | Excel Discussion (Misc queries) | |||
counting the number of times a word appears | Excel Worksheet Functions | |||
Searching for text & counting how many times it appears | Excel Worksheet Functions | |||
how many times a particular day of the week appears in a given month | Excel Discussion (Misc queries) | |||
Counting the number of times a word appears 'anywhere' on a page | New Users to Excel |