Sumproduct Question #3

=SUMPRODUCT(--(H15:H3000=B9)*(G15:G3000=C9),(l15:l3000))

This forumula doesn't quite do what I want.  As it stands it sums the
values in l15:l3000.  What I want it to do is count the number of
values in l15:l3000 providing the criteria is met in the first part of
the forumula.

I'm not quite sure how to do that.  (l15:l3000 will have some blank
cells if that matters.)

0
jimx22 (226)
8/20/2010 5:48:40 PM
excel 39879 articles. 2 followers. Follow

6 Replies
684 Views

Similar Articles

[PageSpeed] 39

Hi Jim,

Am Fri, 20 Aug 2010 10:48:40 -0700 schrieb JimS:

> What I want it to do is count the number of
> values in l15:l3000 providing the criteria is met in the first part of
> the forumula.

=SUMPRODUCT(--(H15:H3000=B9),--(G15:G3000=C9),--(I15:I3000<>""))


Regards
Claus Busch
-- 
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
0
claus_busch (107)
8/20/2010 5:57:27 PM
On Fri, 20 Aug 2010 10:48:40 -0700, JimS <jimx22@msn.com> wrote:

>=SUMPRODUCT(--(H15:H3000=B9)*(G15:G3000=C9),(l15:l3000))
>
>This forumula doesn't quite do what I want.  As it stands it sums the
>values in l15:l3000.  What I want it to do is count the number of
>values in l15:l3000 providing the criteria is met in the first part of
>the forumula.
>
>I'm not quite sure how to do that.  (l15:l3000 will have some blank
>cells if that matters.)

Try:

=SUMPRODUCT((H15:H3000=B9)*(G15:G3000=C9)*(LEN(L15:L3000)>0))



0
ron6368 (329)
8/20/2010 5:58:59 PM
On Fri, 20 Aug 2010 19:57:27 +0200, Claus Busch
<claus_busch@t-online.de> wrote:

>Hi Jim,
>
>Am Fri, 20 Aug 2010 10:48:40 -0700 schrieb JimS:
>
>> What I want it to do is count the number of
>> values in l15:l3000 providing the criteria is met in the first part of
>> the forumula.
>
>=SUMPRODUCT(--(H15:H3000=B9),--(G15:G3000=C9),--(I15:I3000<>""))

>=SUMPRODUCT((H15:H3000=B9)*(G15:G3000=C9)*(LEN(L15:L3000)>0))
>
>
>Regards
>Claus Busch

Both of these formulas work fine.  Thanks to you both.

0
jimx22 (226)
8/20/2010 6:09:32 PM
On Fri, 20 Aug 2010 11:09:32 -0700, JimS <jimx22@msn.com> wrote:

>On Fri, 20 Aug 2010 19:57:27 +0200, Claus Busch
><claus_busch@t-online.de> wrote:
>
>>Hi Jim,
>>
>>Am Fri, 20 Aug 2010 10:48:40 -0700 schrieb JimS:
>>
>>> What I want it to do is count the number of
>>> values in l15:l3000 providing the criteria is met in the first part of
>>> the forumula.
>>
>>=SUMPRODUCT(--(H15:H3000=B9),--(G15:G3000=C9),--(I15:I3000<>""))
>
>>=SUMPRODUCT((H15:H3000=B9)*(G15:G3000=C9)*(LEN(L15:L3000)>0))
>>
>>
>>Regards
>>Claus Busch
>
>Both of these formulas work fine.  Thanks to you both.

Glad to help.  Thanks for the feedback.
0
ron6368 (329)
8/20/2010 6:59:50 PM
On Fri, 20 Aug 2010 19:57:27 +0200, Claus Busch
<claus_busch@t-online.de> wrote:

>Hi Jim,
>
>Am Fri, 20 Aug 2010 10:48:40 -0700 schrieb JimS:
>
>> What I want it to do is count the number of
>> values in l15:l3000 providing the criteria is met in the first part of
>> the forumula.
>
>=SUMPRODUCT(--(H15:H3000=B9),--(G15:G3000=C9),--(I15:I3000<>""))
>
>
>Regards
>Claus Busch


Oops, I found a bug.  If there are three instances of b9 and three
instances of c9, but only one item in the L column it is counting
three instead of one.

The error occurs with both formulas.  Here is the entire forumula;
only the last part, which I have separated doesn't work:

=IF(AND(B9="all",C9="all"),COUNT(L14:L2999),IF(B9="all",SUMPRODUCT(--(G14:G2999=C9),--(L14:L2999<>""))
,IF(C9="all",SUMPRODUCT(--(H14:H2999=B9),--(L14:L2999<>""))


,SUMPRODUCT(--($H$15:$H$3000=B9),--($G$15:$G$3000=C9),--($I$15:$I$3000<>"")))))
0
jimx22 (226)
8/20/2010 7:11:16 PM
On Fri, 20 Aug 2010 12:11:16 -0700, JimS <jimx22@msn.com> wrote:

>On Fri, 20 Aug 2010 19:57:27 +0200, Claus Busch
><claus_busch@t-online.de> wrote:
>
>>Hi Jim,
>>
>>Am Fri, 20 Aug 2010 10:48:40 -0700 schrieb JimS:
>>
>>> What I want it to do is count the number of
>>> values in l15:l3000 providing the criteria is met in the first part of
>>> the forumula.
>>
>>=SUMPRODUCT(--(H15:H3000=B9),--(G15:G3000=C9),--(I15:I3000<>""))
>>
>>
>>Regards
>>Claus Busch
>
>
>Oops, I found a bug.  If there are three instances of b9 and three
>instances of c9, but only one item in the L column it is counting
>three instead of one.
>
>The error occurs with both formulas.  Here is the entire forumula;
>only the last part, which I have separated doesn't work:
>
>=IF(AND(B9="all",C9="all"),COUNT(L14:L2999),IF(B9="all",SUMPRODUCT(--(G14:G2999=C9),--(L14:L2999<>""))
>,IF(C9="all",SUMPRODUCT(--(H14:H2999=B9),--(L14:L2999<>""))
>
>
>,SUMPRODUCT(--($H$15:$H$3000=B9),--($G$15:$G$3000=C9),--($I$15:$I$3000<>"")))))

Found the error.  I had I instead of L.

0
jimx22 (226)
8/20/2010 7:18:44 PM
Reply:

Similar Artilces:

Question about Pictures in Excel #2
Maybe I should explain more in detail...here is what I have... Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cance As Boolean) Dim testStr As String Dim myFileName As String Dim myPict As Picture Set Target = Target(1) myFileName = "C:\Documents and Settings\ahoekst\My Documents\DirectX _ & "\My Pictures\" _ & Me.Cells(Target.Row, "D").Value _ & ".jpg" testStr = "" On Error Resume Next testStr = Dir(myFileName) On Error GoTo 0 If testStr = "" Then MsgBox "file not found" Exit Su...

Missing email #3
For several months now, I have been having a problem with not receiving all of my email. Many, many times, I have learned that someone sent me an email, yet I never received it. Also, emails I send are sometimes not received. I have a small LAN and WLAN and my husband, who sits three feet away, always seems to get his email. I have taken to having him copied on anything important and he always seems to get his copy, but I frequently don't get mine. I have tried eliminating every possible cause I could think of, including the following: 1 - My system: The problem also occ...

Directsound Slaving info question
I am developing an analog capture filter driver. There was an AV sync issue after the long time live source playback (>2 hr). The audio pin always queue few buffers after long time playback. I have checked the video and audio PTS with each media transfer sample, they look like no problem. I have a question with Default Directsound Device filter. There is information in Default Directsound Device Properties->Advanced->Slaving Info->Rate. The "Rate" should be 48KHz with my device, but it will be changed with the "High Err" and "Low Err" param...

CCR disk requirements questions
Well this is throwing me off guys. We are planning a CCR environment. We have an EMC scan at our HQ that we plan to connect our primary mailbox server to. Our DR mailbox server is our concern. I was under the impression that the hardware didnt need to be "the same", but beyond that I was under the impression that they didnt even need to be "all that close" on performance. However, reading this doc: http://technet.microsoft.com/en-us/library/c5a9c0ed-e43e-4bc7-99fe-7d1a9cb967f8.aspx makes it seem like not only does the "passive node" (the server in the DR...

Offline Address Book not updating #3
We use Exchange 2003 and Outlook 200 and 2003. Outlook 2003 use the outlook s in cache mode, be have latey noticed "some" user aren't getting the new GAL after a day or so. We run the Offline Address Book from excahnge at midnight and 1pm. What could be the probem? Mine seems to be fine, be some users are can be 2-4 weeks old... Try taking them off cache mode. 1) Settings -> Control Panel -> Mail -> Email Accounts -> View or Change existing email settings Select Exchange server and click change 2) Check off the cached mode. -> Click Next and finish....

a template question about MFC containers' SerializeElements() ...
Hi, I have the following code compiled in both VC++ 6.0 and .net. Is it safe to remove the #if block and simply use the #else block for both 6.0 and .net? Please help. Thanks for any suggestion. JD class CPerson : public CObject { . . . }; CArray< CPerson, CPerson& > personArray; #if _MSC_VER < 1400 template <> void AFXAPI SerializeElements <CPerson> ( CArchive& ar, CPerson* pNewPersons, int nCount ) #else template <CPerson> void AFXAPI SerializeElements ( CArchive& ar, CPerson* pNewPersons, int nCount ) #endif ...

CRM 3.0 server not available
Hi, I have got a question about CRM 3.0. We had installed CRM 3.0, but when I open Outlook (2003) he give following message: - The server is not available and try to restart Outlook again. I tried it, but the same error appears. What can I do to resolve the problem ? It seems that CRM does not connect with outlook. I reconnected the services, restarting the server...nothing. Who can help me? greetzz Rene reneotter@hotmail.com Rene; When you installed the CRM for Outlook, it asked you for the URL of your CRM server. On that same computer, can you open Internet Explorer and enter t...

Y scale for P. ie when P$= 2, QD=10 & QS=3 P needs to be Y scale
I am trying to create a graph the compares Quantity Demanded and Supplied in relation to a particular price to find the equilibrium (intersection). The problem is, I have no idea how to make one row the Y scale in my graph. and the other two (QD and QS) the two line graphs which should intersect. For example at a price $0, the QD is 11 units and the QS is 0. At a price $ 4 the QD is 9 units and the QS is 3 units. So as price increases/decreases, demand and supply changes! Hi, Maybe Tushar Mehta's page can help you out. http://tushar-mehta.com/excel/charts/supply_and_demand/ Cheers ...

Backup Exchange Server #3
Hi, I am new to Exchange administration. So please be gentle in reply... Here is the Question: My company currently has 1 exchange server. Recently it went down and we did not have emails for almost 3 days before I could figure out a way to get the database back (considering that there was no backup as it was failing). Now it is all working perfectly well. My boss now wants me to put in a secondary exchange server so that incase the first one fails it should automatically switch over to the second one and there should be no down time. My company is based on revenue from orders coming t...

Load event question
I found this code along with it's function on this discussion group. It works great but I want it to populate the text field when the form loads. How can I get the following code on the Form_Load()? fOSUserName() is a defined function. Private Sub Form_BeforeUpdate(Cancel As Integer) On Error GoTo BeforeUpdate_Err ' Set bound controls to system date and time and current user name Me.DateModified = Now() Me.ModifiedBy = fOSUserName() BeforeUpdate_End: Exit Sub BeforeUpdate_Err: MsgBox Err.Description, vbCritical & vbOKOnly, _ &quo...

Beginner question #2
Please forgive what might be a beginner question. I have a spread sheet that is made up of 10 columns. I would like to make it so that each time I change a number in these columns, the sub-total at the bottom of each column changes automatically AND so that the sub-total in the far right column changes automatically. Is there a template for this?? Thanks so much!!! You would use a function for that, lets say you have values in A1 to A10, then in A11 place this =SUM(A1:A10), or =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10. got the idea?? "jenny" <jenny@hotmail.net> skrev i melding ne...

expanding on sumproduct command to add all cases
Thanks to Roger Govier and Paul for previous help! This is a restart of an older thread... I want to expand on the formula below to add together all the instances of "D", "E", and "N" in the range B12:B376, can anyone offer help with doctoring the formula =SUMPRODUCT(--(B12:B376="D")*(A12:A376<=Today())) to do this??? See below for original thread Thanks again, Alan PART 1 I am working on a scheduling system in Excel 2002. In cells A12: A376 I have the dates from 1/1/2004 > 12/31/2004 and in column B12:B376 the characters can be "D"...

money 2001 #3
I had to reload my entire operating system and therefore had to reinstall money 2001. I saved my money folder but do not know how to reinstall my check register. I am fearful that it may not live in the money folder. Does anyone know what it is called and how I can reload that file into my newly installed money 2001? Now's probably not the brightest time to come ask this question. You Money data file is stored ****where you told Money to store it.**** In most cases--and in any sensible case--this won't be in the Program Files\Microsoft Money directory. It will be in the My Doc...

seeking your questions on RMS items--today!
Hi all, I’m the editor of the Microsoft Business Solutions Community site (www.microsoft.com/BusinessSolutions/community). I wanted to let you know that we’re still taking questions on RMS items for our upcoming Q&A with Jimmy Wong, a Microsoft Software Design Engineer in Test (SDET) who works on the Retail Management System team. It doesn't matter how complex or simple your question/concern. Any topic goes, be it discounts, find, inventory, item types, item lookup, pricing, substitutes, or commission. Jimmy's open to any and all questions. :) So if you’ve got a question ...

earn monry 100 dolor cilick mysite #3
earn monry 100 dolor cilick mysite free download song vidios ********************************** htpp;//beautifullykhan.blogspot.com ************************************* ...

Creating rules #3
Does anyone know how I can create a rule that looks for emails that have an attachment with a specific name? You can't. -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "dkennamer" <dkennamer@nwmls.com> wrote in message news:13c3301c44435$ae2d3410$a001280a@phx.gbl... > Does anyone know how I can create a rule that looks for > emails that have an attachment with a specific name? Are you up to writing a macro? I think that's the only way to do it...if you create a...

Question regarding Exchange 5.5 and 2003 running at the same time
I recently upgraded my domain from NT 4.0 to 2003 in mix mode. I still have a few BDCs and Exchange 5.5 running with no problems. To prepare for Exchange 2003 install I ran forest prep, domain prep and ADC connector. Can I install Exchange 2003 with Exchange 5.5 running at the same time. What downtime should I expect? What could go wrong?? If anyone has any insight or comments feel free to pass it on. DC There are a bunch of documents and good books available on that subject. Search on the microsoft site for keywords "5.5 migrate 2003" I followed the instructions carefully,...

A real pickle of a design question
Okie, I've been doing Exchange for quite a long time and I've been involved in many deployments. This one is a real curve ball. Let me explain the scenario and if someone could validate my thinking, that would be great. Currently in place: - One domain we'll call 1stdomain.com with an existing Exchange 2003 organization. - Another domain we'll call 2nddomain.com with an existing Exchange 2003 organization. 1stdomain.com is going to be providing connectivity services and other resources to 2nddomain.com in the near future, but 2nddomain.com wishes to retain management of m...

New Exchange server #3
Hi, I have bought a new server on which to run Exchange 2000. I have 2 x 36GB SAS SCSI and 4 x 146GB SAS SCSI. Following advice form this newsgroup I will configure 3 sets of RAID 1. O/S and Pagefile on 36GB RAID 1 Logs on 146GB RAID 1 Databases on 146GB RAID 1 This server will also have our Intranet which is not heavily used. Which drive should I put that on? Any comments would be appreciated. With our existing Exchange server setup we also have a POP3 Mail server in our DMZ. I want to do away with that server and let Exchange handle all the Mail traffic. Can someone point me to some...

Using Outlook with Hotmail
Hello. I've just started using Outlook 2003, after having owned the disc for a while (I only used office for Word prior to this). I use two hotmail accounts, both of which are installed fine. 1) How can I get an audio alert when new hotmail arrives? Surely if Outlook Express provides one, then the more advanced program should too??? This is a mjor thing for me, as I don't want to have to keep checking constantly to see if I have mail. 2) How do I configure my sent items to stay in the right place - e.g. the hotmail sent items with their relevant accounts, rather than in the un...

AllocSysString question
I created a COM interface. STDMETHODIMP CMyObj::GetName(BSTR* bstrName) { // TODO: Add your implementation code here CString strName = "John Master"; *bstrName = strName.AllocSysString(); return S_OK; } My question here is: 1. In the method, I did AllocSysString(). Does this cause a memory leak since I didn't free it ? Does COM runtime handle this ? 2. This method could be called from within VB or C++. Does the caller need to free the memory ? Thanks in advance. >1. In the method, I did AllocSysString(). Does this cause a memory leak >since I didn't fre...

Help please? #3
sorry thanks its working! : -- H4F ----------------------------------------------------------------------- H4Fi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1625 View this thread: http://www.excelforum.com/showthread.php?threadid=27657 ...

Faxing with CRM 3.0 Professional?
Greetings: Is anyone aware of a solution for faxing from CRM 3.0 Pro? I have checked with z-Firms web site and they only talk about a v1.2 solution. Any thoughts? Thanks I would try RightFax (http://www.captaris.com). It's a pretty powerful/flexible enterprise fax solution. We have it integrated to our ERP system for sending out quotes/orders etc. (No integration to CRM.) ...

Questions about ESEUTIL
I have recently inmplemented mailbox limits and forced my users to start cleaning up their mailboxes and archiving mail from my Exchange 2000 server. My 100GB Information Store (that's the total size of the EDB and STM files) has now shrunk to 50GB of actual data and likely contains 50GB of blank pages. I do weekly online defrags that show that each database has lots of free space. I will never again be allowing users to freely use all the mailbox space they wish, so the actual amount of data will stay around 50GB and the free space in the IS will never get realocated. I believe a...

GP9 to 10 question
I've run a successful upgrade, but I can't find where to give my users access to the personalized lists, specifically for Field Service Modules. I've given them roles as Svc Manager stake holders etc, kept thier old imported roles... All I get is You don't have security to open this window. But I don't see what 'window' to give them access to. Where do I find that? ...