Sumproduct not working

[lhkittle]

Range(A2:A6) has two names and three blank cells.

Range(B2:B6) has the following in each cell(alt-entered)

6AM-9AM Start
11AM-2PM Start
11AM-2PM Start


This returns 0, looking for 2.

=SUMPRODUCT(--(B2:B6="*4PM-6PM Start*")*(A2:A6<>""))

As a test these return 5 so the "*wild card*" is not an issue.

=COUNTIF(B2:B6,"*4PM-6PM Start*")
=COUNTIF(B2:B6,"*4PM-6PM*")

I've tried some other googled versions of countif but can't get them to work either.

Clearly, I'm overlooking the obvious.

Thanks.
Howard

 

[joeu2004]

This returns 0, looking for 2.
=SUMPRODUCT(--(B2:B6="*4PM-6PM Start*")*(A2:A6<>""))

As a test these return 5 so the "*wild card*" is not an issue.
=COUNTIF(B2:B6,"*4PM-6PM Start*")
=COUNTIF(B2:B6,"*4PM-6PM*")

The asterisk never works as a wild-card with any comparison operator. For
example, IF(B2="*4PM-6PM Start*",...) does not do what you might expect.

Wild-card characters work only in specified functions, like COUNTIF.

If you have Excel 2007 or later (please specify in future questions) and
your intent is to count when both conditions are met, you can do:

=COUNTIFS(B2:B6="*4PM-6PM Start*",A2:A6,"<>")

Note the "S" at then of COUNTIFS.

Caveat: The condition "<>" might not work exactly A2:A6<>"" above, IIRC.
Experiment to be sure that it counts all the instances you want, and it does
not count instances you do not want.

Alternatively (works in all Excel versions):

=SUMPRODUCT(ISNUMBER(SEARCH("4PM-6PM Start",B2:B6))*(A2:A6<>""))

Caveat: Test that carefully. I did not.

Note: You do not need double-negation ("--") if you multiply conditions.
Any arithmetic operation is sufficient to change TRUE and FALSE into 1 and
0.

 

[lhkittle]

The asterisk never works as a wild-card with any comparison operator. For

example, IF(B2="*4PM-6PM Start*",...) does not do what you might expect.



Wild-card characters work only in specified functions, like COUNTIF.



If you have Excel 2007 or later (please specify in future questions) and

your intent is to count when both conditions are met, you can do:



=COUNTIFS(B2:B6="*4PM-6PM Start*",A2:A6,"<>")



Note the "S" at then of COUNTIFS.



Caveat: The condition "<>" might not work exactly A2:A6<>"" above, IIRC.

Experiment to be sure that it counts all the instances you want, and it does

not count instances you do not want.



Alternatively (works in all Excel versions):



=SUMPRODUCT(ISNUMBER(SEARCH("4PM-6PM Start",B2:B6))*(A2:A6<>""))

Thanks joeu2004,

I'm using Excel 2010.

This formula errors with "too few arguments".
=COUNTIFS(B2:B6="*4PM-6PM Start*",A2:A6,"<>")

This seems to work well, I will test some more but looks good to me.
=SUMPRODUCT(ISNUMBER(SEARCH("4PM-6PM Start",B2:B6))*(A2:A6<>""))

Thanks again.

Regards,
Howard

 

[joeu2004]

This formula errors with "too few arguments".
=COUNTIFS(B2:B6="*4PM-6PM Start*",A2:A6,"<>")

The "=" is a copy-and-paste typo. The formula is:

=COUNTIFS(B2:B6,"*4PM-6PM Start*",A2:A6,"<>")

 

[lhkittle]

The "=" is a copy-and-paste typo. The formula is:



=COUNTIFS(B2:B6,"*4PM-6PM Start*",A2:A6,"<>")

That does it. Interesting use of- ,A2:A6,"<>")

Thanks,
Howard

 

[joeu2004]

=COUNTIFS(B2:B6,"*4PM-6PM Start*",A2:A6,"<>") [....]
Caveat: The condition "<>" might not work exactly A2:A6<>"" above, IIRC.
Experiment to be sure that it counts all the instances you want, and it
does not count instances you do not want.

Alternatively (works in all Excel versions):
=SUMPRODUCT(ISNUMBER(SEARCH("4PM-6PM Start",B2:B6))*(A2:A6<>""))

COUNTIF(A1:A1,"<>") is 0 only for any empty cells; that is, no formula and
no constant value. It is one for cells whose value is the null string.

SUMPRODUCT(--(A1:A1<>"")) is 0 for empty cells as well as for cells whose
value is the null string.