#### sumproduct between 2 ranges

```I want to count the number of cells whose values fall between 0.40 and 0.61.
I did

=SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))

but the result is not the same as when I manually count the number of cells
so the formula is clearly wrong.

how do I fix?

--
Message posted via http://www.officekb.com
```
 0
forum (466)
7/13/2005 8:26:22 PM
excel.misc 78881 articles. 5 followers.

4 Replies
267 Views

Similar Articles

[PageSpeed] 5

```One way:

=SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))

=sumproduct() likes to work with numbers.  The -- changes true/false to +1/0.

"Patty via OfficeKB.com" wrote:
>
> I want to count the number of cells whose values fall between 0.40 and 0.61.
> I did
>
> =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))
>
> but the result is not the same as when I manually count the number of cells
> so the formula is clearly wrong.
>
> how do I fix?
>
> --
> Message posted via http://www.officekb.com

--

Dave Peterson
```
 0
petersod (12005)
7/13/2005 8:41:52 PM
```Dave,

Side note.  For some reason, if you multiply the expressions yourself (use
an asterisk multiply operator), the resulting TRUE or FALSE values of each
boolean expression get coerced to 1 or 0 automatically, and you don't need
the double negation operators:

=SUMPRODUCT( (M3:M20>0.4) * (M3:M20<0.61) )

In this case we're not really using the PRODUCT part of SUMPRODUCT, since
there's only one argument. It's just a handy array-SUM function.

But if we provide it with two arguments, letting it do the multiplication:

=SUMPRODUCT( (M3:M20>0.4), (M3:M20<0.61) )

It doesn't seem to coerce the resulting TRUE or FALSE values to 1 or 0
before it does the multiply.  So we have to use the double-negation to force
the coercion.

=SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))

I don't know why.  I'm just a pawn in the great game of life.

--
Earl Kiosterud
www.smokeylake.com

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42D57C90.2D9C44A5@verizonXSPAM.net...
> One way:
>
> =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
>
> =sumproduct() likes to work with numbers.  The -- changes true/false to
> +1/0.
>
> "Patty via OfficeKB.com" wrote:
>>
>> I want to count the number of cells whose values fall between 0.40 and
>> 0.61.
>> I did
>>
>> =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))
>>
>> but the result is not the same as when I manually count the number of
>> cells
>> so the formula is clearly wrong.
>>
>> how do I fix?
>>
>> --
>> Message posted via http://www.officekb.com
>
> --
>
> Dave Peterson

```
 0
someone798 (944)
7/14/2005 6:21:47 AM
```From what I've read, the -- version is slightly faster (generally).

And I actually find it easier to understand how the product and sum work in the
=sumproduct() function!

Earl Kiosterud wrote:
>
> Dave,
>
> Side note.  For some reason, if you multiply the expressions yourself (use
> an asterisk multiply operator), the resulting TRUE or FALSE values of each
> boolean expression get coerced to 1 or 0 automatically, and you don't need
> the double negation operators:
>
> =SUMPRODUCT( (M3:M20>0.4) * (M3:M20<0.61) )
>
> In this case we're not really using the PRODUCT part of SUMPRODUCT, since
> there's only one argument. It's just a handy array-SUM function.
>
> But if we provide it with two arguments, letting it do the multiplication:
>
> =SUMPRODUCT( (M3:M20>0.4), (M3:M20<0.61) )
>
> It doesn't seem to coerce the resulting TRUE or FALSE values to 1 or 0
> before it does the multiply.  So we have to use the double-negation to force
> the coercion.
>
> =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
>
> I don't know why.  I'm just a pawn in the great game of life.
>
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42D57C90.2D9C44A5@verizonXSPAM.net...
> > One way:
> >
> > =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
> >
> > =sumproduct() likes to work with numbers.  The -- changes true/false to
> > +1/0.
> >
> > "Patty via OfficeKB.com" wrote:
> >>
> >> I want to count the number of cells whose values fall between 0.40 and
> >> 0.61.
> >> I did
> >>
> >> =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))
> >>
> >> but the result is not the same as when I manually count the number of
> >> cells
> >> so the formula is clearly wrong.
> >>
> >> how do I fix?
> >>
> >> --
> >> Message posted via http://www.officekb.com
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
```
 0
petersod (12005)
7/14/2005 1:00:20 PM
```Dave,

I agree.  It's a little weird to be using SUMPRODUCT, then still have to use
multiply operators.  It also seems odd that when using two or more arguments
(your way), it doesn't coerce the TRUE and FALSE.  Why do I hear Twilight
Zone music in the background?
--
Earl Kiosterud
www.smokeylake.com

Off topic:  Anyone who hasn't Men in Coats,
http://www.koreus.com/files/200505/men-in-coats.html should do so.  It's a
riot.

----------------------------------------------------------------------------------------

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42D661E4.E024C8B3@verizonXSPAM.net...
> From what I've read, the -- version is slightly faster (generally).
>
> And I actually find it easier to understand how the product and sum work
> in the
> =sumproduct() function!
>
> Earl Kiosterud wrote:
>>
>> Dave,
>>
>> Side note.  For some reason, if you multiply the expressions yourself
>> (use
>> an asterisk multiply operator), the resulting TRUE or FALSE values of
>> each
>> boolean expression get coerced to 1 or 0 automatically, and you don't
>> need
>> the double negation operators:
>>
>> =SUMPRODUCT( (M3:M20>0.4) * (M3:M20<0.61) )
>>
>> In this case we're not really using the PRODUCT part of SUMPRODUCT, since
>> there's only one argument. It's just a handy array-SUM function.
>>
>> But if we provide it with two arguments, letting it do the
>> multiplication:
>>
>> =SUMPRODUCT( (M3:M20>0.4), (M3:M20<0.61) )
>>
>> It doesn't seem to coerce the resulting TRUE or FALSE values to 1 or 0
>> before it does the multiply.  So we have to use the double-negation to
>> force
>> the coercion.
>>
>> =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
>>
>> I don't know why.  I'm just a pawn in the great game of life.
>>
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:42D57C90.2D9C44A5@verizonXSPAM.net...
>> > One way:
>> >
>> > =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
>> >
>> > =sumproduct() likes to work with numbers.  The -- changes true/false to
>> > +1/0.
>> >
>> > "Patty via OfficeKB.com" wrote:
>> >>
>> >> I want to count the number of cells whose values fall between 0.40 and
>> >> 0.61.
>> >> I did
>> >>
>> >> =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))
>> >>
>> >> but the result is not the same as when I manually count the number of
>> >> cells
>> >> so the formula is clearly wrong.
>> >>
>> >> how do I fix?
>> >>
>> >> --
>> >> Message posted via http://www.officekb.com
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

```
 0
someone798 (944)
7/14/2005 7:53:05 PM

Similar Artilces:

Outlook Web Access #2
I have Exchange 5.5 SP4 and Outlook Web Access installed on the Member Windows NT 4.0 Server. If the regular domain user logs in to Outlook Web Access he can see a list of mail messages but when he opens any of them they look like empty, but actually they are not. If user the member of Administrators Group logs in to the Outlook Web Access he is fine. What should I change? ...

Compare entries in 2 worksheets and list what does not match
Good Day All; I have 2 Excell works sheets with approx 14000 rows each. What I would like to do is compare both lists and get a 3rd list that shows what entries do not match. Is there a simple way to do ythis in Excel Thanks All Chomp Assuming A1 should equal A1 in the other sheet... =IF(Sheet1!A1=Sheet2!A1,"",Sheet!A1&" does not match "&Sheet2!A1) Auto-Filter for non-blanks. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "The Chomp" wrote: > Good Day All; > > I have 2 ...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

How to share same email a/c on 2 pc
I have linked 2 pc via Windows Small Office network. How to let these 2 pc share the same email a/c in Microsoft Outlook. Plse provide solution in detailed steps as I am a novice. Operating syste=xp outlook version 2003 Thanks "Christine" <centrol@singnet.com.sg> wrote in message news:hop8fj\$7la\$1@reader01.singnet.com.sg... > I have linked 2 pc via Windows Small Office network. > > How to let these 2 pc share the same email a/c in Microsoft Outlook. > > Plse provide solution in detailed steps as I am a novice. > > Operating sys...

Scribe Migrate
I am planning to do a reinstall on our CRM server and have purchased Scribe (didn't come in till JUST now), and was planning to do this over the weekend. I have a test environment and want to try it here before I do this on our production. However this isn't as simple as I thought. I know, I know, read the manual, etc, etc etc. But its near critical that I get this production reinstalled, and I'd rather not work straight through a weekend just to learn this thing. Anways, can anyone give me the basic overview of this? Does this take hours just to create the migration setup? For s...

testing (OT) 2
This is a multi-part message in MIME format. ------=_NextPart_000_004E_01C8B5B7.029A2C00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I put this post on using the google group version, now using the = Microsoft newsgroup server. I'm trying to pin down what works and what = doesn't and why this specific sign in ID doesn't work on google groups = OR on the news server I use in Outlook Express. But another ID I have = does. I'm posting this using the Microsoft newsgroup server, which is = something else, and should work....

JPG Files #2
Just installed Outlook 2003. Can no longer view jpg files...either those sent to me or ones I try and attach??? Have cleaned out my temp internet files...hELPPPPPPPPPPPPPPPPPPPPP Thx Make sure you have a default program set for opening jpg-files. Rightclick the file from within Windows Explorer nd choose Open With... and make sure you select to always open that file with the selected program You probably had set Microsoft Photo Handler as thedefault program which has been replaced by Picture Manager. -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Cre...

Cannot close opportunities #2
I upgraded from CRM 3.0 to 4.0 We encountered issues with SQRSS - I had to uninstall and reinstall SQRSS. I am now encoutering issues when closing a new opportunity it error out throwing an Exception of type "System.web.HttpUnhandledException' Full Stack: [NullReferenceException: Object reference not set to an instance of an object.] at Microsoft.crm.dialogs.CloseOppPage.ConfigureForm(_ at Microsoft.crm.Application.Controls.AppUIPage.OnPreRender(EventArgs e) at system.web.UI.Control.PreRenderRecursiveInernal() at System.web.UI.Page.ProcessRequestMain(Boolean includesStagesBefore A...

New entries do not appear in address book contents list. Thought it might be a space problem, but have deleted all extraneous data (i.e. deleted items. However, when I try to re-enter the contact, Outlook asks if I wish to replace the earlier entry for the same contact. Any ideas? Explain more accurately what you mean. Where are entries not appearing that you expect them to? Are you selecting the correct Contacts Folder from the "Show names from..." dropdown? -- Russ Valentine [MVP-Outlook] "Lulu" <anonymous@discussions.microsoft.com> wrote in message news:88020...

Why does 62 KB translate into 4.2 MB in Publisher?
When I added a 62 KB jpeg to my page, the overall file size jumped from ~275 KB to 4.2 MB. What gives? Let me guess - Publisher 2000? -- JoAnn Paules MVP Microsoft [Publisher] "Publisher induced baldness" <Publisher induced baldness@discussions.microsoft.com> wrote in message news:9ED85165-A29E-44AA-AFE6-4F5A5D518991@microsoft.com... > When I added a 62 KB jpeg to my page, the overall file size jumped from > ~275 > KB to 4.2 MB. What gives? Publisher 2000 doesn't support compression. PUB2000: File Size Increases Unexpectedly When You Insert Picture ...

Green triangles #2
thanks for such a speedy response, teh placement of 1 in cell an edit,paste special, multiply works, but why? the value function doesn't work, as the green triangle seems to rende the contents of the cell as neither number or text. strange. once again thanks : -- Stevethedo ----------------------------------------------------------------------- Stevethedoc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=27800 Happy to help. Thanks for the feedback. As to why one works and not the ot...

After 12.2.4 update, cannot open some Word docs with Open passwords
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel After installing the 12.2.4 Office update, I am unable to open some Word files that I had password-protected with an Open File password. How can I open? Thanks Basically, "you can't". Sorry, the other passwords are crackable, but "Password to Open" is effectively the encryption key. The entire file is encrypted, and unless you have the correct password, you can't get it open. However, are you sure you are not attempting to open files that were password-=protected on the PC? ...

Why is Excel changing the last 2 digits of a 17 digit num to 00.
When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to 00 when I leave the cell. Format - Cell does not have a setting to stop this 'feature'. How do I make Excel recongize the large number? On Thu, 28 Jul 2005 19:09:01 -0700, "Allie" <Allie@discussions.microsoft.com> wrote: >When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to >00 when I leave the cell. Format - Cell does not have a setting to stop this >'feature'. Excel Specifications and Limits: Calculation specifications Feature Maximu...

Purchase Order 2.0.0127
Am I the only one having issues with the upgrade? 1) I create a purchase order 2) I click on add item 3) I type in the supplier ILC, and nothing comes up (ONLY on some items)! 4) I click on all items, and find the item, and add it! 5) When I click on OK the item is added to the purchase order, along with the supplier ILC! OR When I click on all items, even though the item may be in the database, I can't find it to add to the PO, unless I close out the PO and re-open it??? This started happening only since I upgraded the the 2.0.0127! -- Thank You Vince :) -- Thank You Vince :) Yes...

Integrating Great Plains 8.0 and CRM 1.2 and Windows Small Busines
Integrating Great Plains 8.0 and CRM 1.2 I run a Windows Small Business Server 2003 Premium box along with a Windows Server 2003 Standard box used for my Great Plains 8.0 and SQL 2000. I’m considering introducing CRM 1.2 Professional into the mix. From a hardware stand point I know I’m fine all around. I’ve been researching how I should configure CRM and am a bit confused. The info I’ve found in KB887153 states “When you install Microsoft Business Solutions CRM Integrations for Great Plains 7.0, 7.5, or 8.0, Microsoft BizTalk Server is also installed. However, BizTalk Server 2000 and B...

SK 40175 Tuff-1 1/2-Inch Drive 15-Inch Reversible Ratchet
Price:\$90.24 Image: http://thediscounfinder.info/image.php?id=B0009U5RRM Best deal: http://thediscounfinder.info/index.php?id=B0009U5RRM Item #: SKT40175. Features & Benefits:Tuff 1(r) Ratchets 72 tooth head design delivers a short 5" stroke for use in tight areas.17 teeth engage when torque is applied to provide a strong grip without slippage.Thumb falls naturally into position, giving instant control with one hand.Easy to reverse, 4 point cap.SuperKrome plating for long tool life and maximum corrosion resistance.Description: 15" Reversible Tuff Customers also search for:...

Sumproduct usage?
My brain's on overload at the moment, so I'm turning to you guys for help. I need help w/ a formula that will count the number of sales that someone had based on a certain date...I thought I could use sumproduct, but I'm beginning to wonder if I was wrong about that. The relavant columns are D & M. Col D Col M 12/24/2009 Andy 12/24/2009 Charles 12/24/2009 Andy 12/25/2009 Charles You had the right function in mind. Try something like this... Use cells to hold the criteria: A1 = some date B1 = some name =SUMP...

RMS 2.0 matrix dimensions are annoying, but help is available
For reasons I don't understand, MS saw fit in RMS 2.0 to use dimensions data for matrix components that is far less accessible to users than Sub Descriptions are. For instance, I can't edit assign a dimension value to an existing item I have added to a matrix. I can't see a reason for using Dimensions with limitations like this as using Sub Descs. to describe matrix "dimensions" worked fine previously. Does anyone know why MS did this? It's annoying! Digital Retail Solutions (DRS) has a product called Power Ops (Build 2.2.0003). It's help file mentions (se...

Printing a blank cell if sumproduct formula not true
If I have a formula such as =sumproduct((A1:A5=B2)* (C1:C5=D2))how can I get the cell that the formula is in to print blank if the formula is not true? Thanks for any help. > ...if the formula is not true? I might be wrong, but it looks to me that you are doing a "Count" of how many matches there are in Parallel. In Excel, True * True = 1. Would it be correct to say if the "Count" is zero, then hide the results? Perhaps a custom format that hides zero values. Say "General;General;", or something similar. The last ";" will hide zero values. --...

Keyboard Shortcut #2
Is there a keyboard short cut or quick way to switch between tabs or worksheets in an Excel workbook? Thanks Steve Switch tab: Ctrl + PageUp / PageDown Switch workbook: Ctrl + Tab / Shift + Ctrl + Tab (backward) HTH "Steve Cohen" <sacohen@bellsouth.net> �b�l�� news:Gjohb.33943\$9a7.17809@bignews6.bellsouth.net �����g... > Is there a keyboard short cut or quick way to switch between tabs or > worksheets in an Excel workbook? > > Thanks > > Steve > > Here are two links that provide info on more keyboard shortcuts. http://www.cpearson.com/excel/...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Can you link 2 worksheets together?
Say i have one worksheet and on my second one I want to reference cells from the first one? is there a formula for that>? To create a simple link: Select a cell in the second worksheet Type an equal sign Select the first sheet Click on the cell that you want to link Press the Enter key. Alesha wrote: > Say i have one worksheet and on my second one I want to reference cells from > the first one? is there a formula for that>? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Exclude outbound Internet mail from journaling #2
I have an Exchange 5.5 installation which requires message journaling of internal email only. External (Internet) email is captured and archived by a gateway SMTP server, whilst internal mail is journaled to the same SMTP server for archiving. The documentation regarding journaling on Exchange 5.5 details how to route local messages through the MTA, and how to route Internet Mail Service messages through the private information store, but it seems even when this second routing configuration is not applied, outbound internet mail is still being journaled. I need a way to ONLY journal ...

zip code help! #2
thanks for the help !!!!!!!! It worke -- cbender ----------------------------------------------------------------------- cbender3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1556 View this thread: http://www.excelforum.com/showthread.php?threadid=27126 ...

Converting from Lotus 1, 2, 3 docs
I have a user who has a bunch of Lotus 1, 2, 3 spreadsheets that he is trying to convert to Excel. His version of Lotus is old (version 5), his version of Excel is 2000 sp2. The files convert but they are missing pages or headings or other unacceptable qualities. Anyone any advice? Michael If you open the files using Excel and save them to an .xls format in the current version you are losing data? A while back I needed to do this. I wrote an application in VB that loops through the files, opens them using Excel (add a reference in VB to MS Office 10) and then save them in the current ...