Hey guys,
I'm trying to count the number cells in a range('Nonconformance Table'!$F$3:$F$1000) that match the year value of the date in cell $Y$2. so far I have the following, but it returns #VALUE!;
If anyone has any ideas, that would be great
Please Login or Register to view this content.
Last edited by mr_mango81; 05-04-2017 at 12:54 AM.
Have you tried array entering it?
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Dave
Thanks Dave, but that still seems to return the same '#VALUE!'. I guess the outcome I'm after would be like a COUNTIF where it counts the number of values in the range that match the year in the date value of cell 'Y2', except I will need to add a third criteria at a later point, hence trying to use SUMPRODUCT.
Justin
Justin I'm puzzled because that formula should work now unless there are any dates in those references that are not numbers but text "dates".
If you were not aware of it dates are numbered days starting with day 1 on 1/1/1900. The formatting is cosmetic. While today's date is 5/3/2017 as far as we humans see (humans in the western hemisphere right now) Excel "sees" 42858. All text however is 0.
To test this go to G3 enter this formula and fill down =ISNUMBER(F3). Reference likewise Y1. If any of them returns FALSE you've found your culprit.
Last edited by FlameRetired; 05-03-2017 at 10:55 PM.
Yeah, it's got me puzzled too. All the cells are formatted as dates on both sheets. The formula seems to work when I select a range from the same sheet the formula is on, but when I choose the range from a different sheet, it doesn't work. Could it possibly have something to do with the sheet reference?
The formatting does nothing to the values.
Did you try the ISNUMBER formula I suggested?
Just checked the range and one cell had a text value in it. Thank you for your help mate. That was driving me crazy!
I'll bet it was. Those usually do that. Glad you found it. You are welcome and thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks