Hello All,
Please see attached.
Cell N40 is supposed to look at sheet 2 and return a total of 3, but it's returning 5. Why? It's throwing my counts off.
The formula is counting dates. =COUNTIFS(sheet2!BJ:BJ,"<>0",sheet2!J:J,5)
Hello All,
Please see attached.
Cell N40 is supposed to look at sheet 2 and return a total of 3, but it's returning 5. Why? It's throwing my counts off.
The formula is counting dates. =COUNTIFS(sheet2!BJ:BJ,"<>0",sheet2!J:J,5)
Sorry - looked at the wrong formula.
Cells BJ5 and BJ6 are the reason why.
Last edited by AliGW; 02-14-2017 at 01:43 PM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Cells BJ5 and BJ6 are the reason why. They are blank.
Thank you. What is wrong with those cells? They are empty.
Yes. That's why they are counted. All the other seemingly blank cells contain the 00/01/1900 date which counts as 0. So your formula is working correctly, i.e. those two cells are not equal to 0.
Yes, but the other empty-looking cells in column BJ actually contain zero, which is displayed as 00/01/1900 in the formula bar - you presumably have set up the sheet to display zeros as blanks. You can change the formula in N40 to this:
=COUNTIFS(sheet2!BJ:BJ,"?*",sheet2!J:J,5)
Hope this helps.
Pete
Those are wildcard characters - the ? matches any single character (so avoids the blanks) and the * matches any number of (or none) characters.
Thanks for the rep.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks