Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default Sumproduct not working

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default Sumproduct not working

wrote:
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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default Sumproduct not working



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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default Sumproduct not working

wrote:
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,"<")

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default Sumproduct not working

On Tuesday, September 17, 2013 7:28:16 PM UTC-7, joeu2004 wrote:
wrote:

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,"<")


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

Thanks,
Howard


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default Sumproduct not working

"joeu2004" wrote (with errata):
=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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sumproduct not working bigproblem Excel Discussion (Misc queries) 2 March 9th 10 12:57 AM
Sumproduct not working Curtis Excel Worksheet Functions 3 November 6th 09 05:20 AM
SUMPRODUCT not working. Gaurav[_4_] Excel Worksheet Functions 2 June 25th 09 09:37 PM
SUMPRODUCT Not Working dj479794 Excel Discussion (Misc queries) 2 March 12th 07 01:54 PM
sumproduct not working Tester Excel Worksheet Functions 10 November 26th 06 10:58 PM


All times are GMT +1. The time now is 11:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"