Countifs formula not calculating my specific data

  • Thread starter Thread starter Derrick
  • Start date Start date
D

Derrick

I am working with a Countifs formula, and it is not returning the correct
information when I am using the specific data I want, but if I simplify it,
it will return what I want. i.e.

=countifs(A3:A500,"1/?",B3:B500,"DAYTONA",C3:C500,"SMITH, JOHN")

^THIS IS NOT WORKING, BUT......

=COUNTIFS(A3:A500,"X",B3:B500,"Y",C3:C500,"Z") does work, why is it doing
this? Would truley appreciate any help.
 
=countifs(A3:A500,"1/?",B3:B500,"DAYTONA",C3:C500,"SMITH, JOHN")
^THIS IS NOT WORKING
Click to expand...

What does not working mean? You get an error? An incorrect result? No
result?

What's in A3:A500?

Is "?" supposed to be a wildcard? If so, what does "1/?" respresent? Is is
supposed to represent a variable date?
 
I do apologize, I should have been more clear. You'll have to excuse me I am
relatively new to this.

a3:a500 will be dates as they are filled in. This spreadsheet is going to
be used as an on going tracking system for us. What I am trying to do is
count any tows that happened in a given month by a location and person doing
the tow.

When I say it doesn't work, it is giving me a 0 in the cell when I know the
information is in the appropriate cells. Therefore confusing me! I do want
a wildcard for the date if that is possible, but not really sure how that
works.
 
Ok...

Wildcards only work on TEXT. In Excel dates are really NUMBERS formatted to
look like dates so the wildcard can't be used. Also, you'll have to use a
different function for this.

Try something like this:

=SUMPRODUCT(--(A3:A500<>""),--(MONTH(A3:A500)=n),--(B3:B500="DAYTONA"),--(C3:C500="SMITH,
JOHN"))

Where n = the month number. Jan = 1 through Dec = 12

Better if you use cells to hold the criteria.

A1 = the month name as a TEXT string = Jan
B1 = Daytona
C1 = Smith, John

Then:

=SUMPRODUCT(--(A3:500<>""),--(TEXT(A3:A500,"mmm")=A1),--(B3:B500=B1),--(C3:C500=C1))
 
That works for my purposes! Thank you so much for your help and patience!
I bet it can get frustrating!

Seriously thank you!
Derrick
 
Back
Top