Formula works for one set of criteria but not the other.

Johnette Connelley
Johnette Connelley used Ask the Experts™
on
I have a formula that works with one set of criteria, but not the other for some reason.  I have attached an example of the file.
Basically, this is a list of sewer and water taps sold.  The formula is a simple "countifs" and the criteria is if its a sewer tap sold between July 1 thru July 31, then count it. The formula for the sewer count works perfectly and counts 57 of them.  The water formula should count the 8 water accounts, but doesn't.  Its the SAME formula with only the one criteria changed.  I have checked for extra spaces, I have checked formatting and all of the usual suspects that cause these problems.
Experts-Exc-Example-Formula-Not-wor.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
Johnette

There are spaces after 'Water'.

Try using a wildcard in the formula, that will save you having to change the data.

=COUNTIFS(TapPdType,"water*",TapPdDt,">="&DATE(2019,7,1),TapPdDt,"<="&DATE(2019,7,31))
Rob HensonFinance Analyst

Commented:
The values in rows 59 to 66 for water all have a space after the word water
Johnette ConnelleyBudget Analyst / Projects

Author

Commented:
THAT WAS IT!  Thank you so much!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial