Why count ifs formula returns zero

[flare9x]

Hello all

I have this formula:

=COUNTIFS(B3:B23496,"Monday",M3:M23496,"True")

The logic looks correct - however the result is zero.... not sure why

B is text... Monday to Friday.... and then column M is and =if the number is higher than yesterday say true or false...

any ideas?

 

[Marcelo Branco]

Check
1. if the values in B3:B23496 are texts or dates formatted as dddd
Try in an empty cell
=ISTEXT(B3)
copy down to, say, 10 rows

2. if the values in in M3:M23496 are texts or logical TRUE/FALSE
try
=ISTEXT(M3)
copy down

Tell us the results

M.

 

[liveinhope]

I like to break a formula that isn't doing what I expect down into small steps so as well as your current formula =COUNTIFS(B3:B23496,"Monday",M3:M23496,"True")

try these =COUNTIFS(B3:B23496,"Monday") and =COUNTIFS(M3:M23496,"True") in new cells



<strike></strike>

 

[AliGW]

You could also try this:

=COUNTIFS(B3:B23496,"*Monday*",M3:M23496,"*True*")

If it works, then it's a case of leading or trailing spaces.

 

[flare9x]

Check
1. if the values in B3:B23496 are texts or dates formatted as dddd
Try in an empty cell
=ISTEXT(B3)
copy down to, say, 10 rows

2. if the values in in M3:M23496 are texts or logical TRUE/FALSE
try
=ISTEXT(M3)
copy down

Tell us the results

M.

M=True
B = False

Copied all the contents of B to a text file.. copied back... now - TRUE

Now formula says:

Monday

#VALUE!

<tbody>
</tbody>

 

[AliGW]

Have you tried either of the other suggestions? If so, what was the outcome?

 

[Aladin Akyurek]

If those TRUE's (and FALSE's) in M are not text, the following should work as intended:

=COUNTIFS(B3:B23496,"Monday",M3:M23496,TRUE)

 

[Marcelo Branco]

M=True
B = False

Copied all the contents of B to a text file.. copied back... now - TRUE

Now formula says:

Monday

#VALUE!

<tbody>
</tbody>

No need to change your original data.

It seems that column B contains dates formatted as dddd and column M contains text values.
So, keeping data as they are, try
=SUMPRODUCT(--(WEEKDAY(B3:B23496)=2),--(M3:M23496="TRUE"))

Hope this helps

M.

 

[flare9x]

No need to change your original data.

It seems that column B contains dates formatted as dddd and column M contains text values.
So, keeping data as they are, try
=SUMPRODUCT(--(WEEKDAY(B3:B23496)=2),--(M3:M23496="TRUE"))

Hope this helps

M.

Yes correct column B is dddd custom format. To create the data in column B this is the formula:


=IFERROR(DATE(LEFT(A2325,4),MID(A2325,5,2),RIGHT(A2325,2)),"")


That convers a number date in yyyymmdd to a day of the week.


So now looking for the countif formula to be able to read column B.


The result of the sum product formula:


Monday #VALUE!




Right now the IFError fields are FALSE when I run IStext()


So let me try and see if I can paste the values from a text file and store as text and see if the sum product formula will work

 

[flare9x]

Really not sure here...

istext() on both columns = True so i tried:

=COUNTIFS(B4:B23496,"Monday",M4:M23496,"TRUE")

I also tried to remove the " " quotations... with no luck either.

With both columns text = true the result is 0 and not #Value.