I have a spreadsheet I'm trying to improve. What i need is a way to count cells that were shipped in 2009 (column I) and have a receive date (column N).
Column "I" shows the Shipped Date, Column "N" shows a received date only if the item has been received.
For example, I need to be able to count how many items shipped in 2009 were received.
Any help would be greatly appreciated.
Last edited by jmorris462; 11-22-2011 at 09:31 AM.
Try:
=SUMPRODUCT(--(YEAR(I2:I100)=2009),--(ISNUMBER(N2:N100))
adjust ranges to suit.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
i tried this and all i could manage to get was a #VALUE. Any other ideas?
Is there text in any of the I column cells? Are they really dates. If you click on them, what appears in the formula bar? Select column I and go to Data|Text to columns and click Finish. Does anything happen?
Can you post a sample workbook showing the problem?
This is an example of the file i am working with. I need to be able to have a cell show me the number of printers shipped in 2009 that have a receive date
It is difficult to diagnose with a bitmap. Can you attach an actual spreadshet
OK, hopefully this works.
With this formula, I get a result of 8:
=SUMPRODUCT(--(YEAR($A$2:$A$19)=2009),--(ISNUMBER($B$2:$B$19)))
Make sure you do not include the A1 and B1 cells in your ranges as they are text....
If you add a column with just year, your problem can be solved even more easily
OK it works!! The problem i was facing was that i have 9000+ cells im working with and about 500 or so of them have an "N/A" in them and they are scattered. Without the N/As it works exactly like i was hoping it would!!
Thank you!!
One last question, is there a way to get around the N/A's or do i need to go thru and remove them all?
use IFERROR to get rid of the NA
IFERROR("Formula Here", "")
Using same sample range, you can try:
which needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER to work.
Please Login or Register to view this content.
Also, it cannot be placed in a merged cell.
Perfect!! Thank you for all the help!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks