So I'm attempting to count how many projects are due in a given month using a countif formula. This may not be the best way of doing this and you may be able to let me know a better way, or why this formula is stating "0". I believe it may be because Im using a formula to get the projected due date??? Anyhow, see attached worksheet and thanks in advance...
James
try something like this:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Your data appears to be held in a different file, COUNTIF will not work referencing data in an external file.
You can use SUM/IF or SUMPRODUCT to achieve the result you need.
Note: The SUM/IF needs to be entered with Ctrl+Shift+Enter as it's an array formula, the SUMPRODUCT can just be entered as normal.
Please Login or Register to view this content.
This worked! Thank you.
You're welcome. Thanks for the rep.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
All of the months worked except for January. It is coming back as a number that isn't possible. my range on the worksheet is 2500 cells in column K. When I shrink the array down to active cells it shrinks the number significantly, but still doesn't make sense. Im guessing that its a formatting error, but have spent probably to much time to not ask for suggestions...
Try
=SUMPRODUCT((MONTH(Sheet1!$B$2:$B$7)=1)*(Sheet1!$B$2:$B$7<>""))
This fixed it. Thank you for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks