SUMPRODUCT() problem

Hi!

I try to get this formula or someting like it to work.

      =SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not OK")*(H17:H1115=F2))


The problem is in  the H-Column. It contains Dates and times like for 
instance 26-04-2007 11:36:34
F2 in the formula contains 26-04-2007, and this is where the problem arises. 
No values in H-column contains this exact value, so the formula retunrs a 
zero. I have tried using VALUE() combined with INT(), and other combinations 
on the h-column, but with no result aso far.

How should my formula look if I want to calulate the number of tests, done 
by Dept. 245-B (M-column) on a certain date (H-column) no matter what time 
of day, with status Not OK (R-column)

Jan


0
Jan
12/5/2007 2:08:42 PM
excel 39879 articles. 2 followers. Follow

3 Replies
606 Views

Similar Articles

[PageSpeed] 2

Hi

Try
=SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not 
OK")*(TEXT(H17:H1115,"dd-mm-yyyy")=F2))

-- 

Regards
Roger Govier

"Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message 
news:#BsSUh0NIHA.5524@TK2MSFTNGP05.phx.gbl...
> Hi!
>
> I try to get this formula or someting like it to work.
>
>      =SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not OK")*(H17:H1115=F2))
>
>
> The problem is in  the H-Column. It contains Dates and times like for 
> instance 26-04-2007 11:36:34
> F2 in the formula contains 26-04-2007, and this is where the problem 
> arises. No values in H-column contains this exact value, so the formula 
> retunrs a zero. I have tried using VALUE() combined with INT(), and other 
> combinations on the h-column, but with no result aso far.
>
> How should my formula look if I want to calulate the number of tests, done 
> by Dept. 245-B (M-column) on a certain date (H-column) no matter what time 
> of day, with status Not OK (R-column)
>
> Jan
>
> 
0
Roger
12/5/2007 2:25:33 PM
Thnak you. It almost did it: :-9

      =SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not 
OK")*(TEXT(H17:H1115,"dd-mm-yyyy")=TEXT(F2,"dd-mm-yyyy")))


was the solution.

Jan

"Roger Govier" <roger@technology4unospamdotcodotuk> skrev i en meddelelse 
news:O8C33q0NIHA.484@TK2MSFTNGP06.phx.gbl...
> Hi
>
> Try
> =SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not 
> OK")*(TEXT(H17:H1115,"dd-mm-yyyy")=F2))
>
> -- 
>
> Regards
> Roger Govier
>
> "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message 
> news:#BsSUh0NIHA.5524@TK2MSFTNGP05.phx.gbl...
>> Hi!
>>
>> I try to get this formula or someting like it to work.
>>
>>      =SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not OK")*(H17:H1115=F2))
>>
>>
>> The problem is in  the H-Column. It contains Dates and times like for 
>> instance 26-04-2007 11:36:34
>> F2 in the formula contains 26-04-2007, and this is where the problem 
>> arises. No values in H-column contains this exact value, so the formula 
>> retunrs a zero. I have tried using VALUE() combined with INT(), and other 
>> combinations on the h-column, but with no result aso far.
>>
>> How should my formula look if I want to calulate the number of tests, 
>> done by Dept. 245-B (M-column) on a certain date (H-column) no matter 
>> what time of day, with status Not OK (R-column)
>>
>> Jan
>>
>> 


0
Jan
12/5/2007 2:36:49 PM
I bet you meant:

=SUMPRODUCT((M17:M1115="245-B")
           *(R17:R1115="Not OK")
           *(TEXT(H17:H1115,"dd-mm-yyyy")=text(F2,"dd-mm-yyyy")))

Another option to ignore times in both column H and F2:

=SUMPRODUCT((M17:M1115="245-B")
           *(R17:R1115="Not OK")
           *(int(H17:H1115)=int(f2)))



Roger Govier wrote:
> 
> Hi
> 
> Try
> =SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not
> OK")*(TEXT(H17:H1115,"dd-mm-yyyy")=F2))
> 
> --
> 
> Regards
> Roger Govier
> 
> "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
> news:#BsSUh0NIHA.5524@TK2MSFTNGP05.phx.gbl...
> > Hi!
> >
> > I try to get this formula or someting like it to work.
> >
> >      =SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not OK")*(H17:H1115=F2))
> >
> >
> > The problem is in  the H-Column. It contains Dates and times like for
> > instance 26-04-2007 11:36:34
> > F2 in the formula contains 26-04-2007, and this is where the problem
> > arises. No values in H-column contains this exact value, so the formula
> > retunrs a zero. I have tried using VALUE() combined with INT(), and other
> > combinations on the h-column, but with no result aso far.
> >
> > How should my formula look if I want to calulate the number of tests, done
> > by Dept. 245-B (M-column) on a certain date (H-column) no matter what time
> > of day, with status Not OK (R-column)
> >
> > Jan
> >
> >

-- 

Dave Peterson
0
petersod (12005)
12/5/2007 2:39:40 PM
Reply:

Similar Artilces:

Very odd Modeless Dialog Window Size problem
Hello community, I have a very strange problem with the size of a modeless dialog window (it actually is a status window derived from CDialog). The dialog window is inside an own UI thread in a dll. I have written the all of the dll, but not the application calling my dll. I do not have the source of the application, neither a handle to its window. When I debug my dll, I have set the executable for the debug session in VC++, so VC++ runs the application when I press F5. In this case, the dialog window looks fine. However, when I start the application from the start menu (not from VC++), the...

problem with loan interest and principal
I have a 10 year student loan I'm paying off. I set it up correctly and each month the amount was entered and the interest amt declined and the principal increased and the payment numbers advanced as they should. Then suddenly it stopped doing that and got stuck on payment number 3 and keeps giving me the same principal and interest amt. It has done this now for about 7 months, except for one month when it suddenly gave me a payment number 6. I've looked at the loan account settings and everything looks ok for the original amount and the interest rate,etc. When I look at the amortizat...

Report formatting problem
I have the following data in an external file: Dept Salesperson Sales$ ---- ----------- ------ Auto John Doe 1000 Auto Jane Doe 1000 Auto David Smith 1000 Sports Mike Brown 1000 Sports Bob Smith 1000 I would like to format it in Excel so that it appears as follows: Auto John Doe 1000 Jane Doe 1000 David Smith 1000 Sports Mike Brown 1000 Bob Smith 1000 Is there a way to do this? Thank you. Hi Try using a Pivot Table to summarise your data. Take a look here for help on getting started. http://peltiertech.com/Excel/Pivots/pivotstart.htm Regards Roger Govier super_doodie@hotmail.com ...

database problem.
I create a mfc dialog programe in vs.net 2003 use vc++ . In the dialog I add a datagrid(sp4,com) and a adodc(sp5,com) but when I close the dialog ,it give me a Error,why?(I had set datagrid's datafield) in VC 6.0(sp5),Everything is OK!why,who can give me a answer? "lxg" <lixngang@163.com> wrote in message news:OP5yi4W6DHA.2748@TK2MSFTNGP09.phx.gbl... > I create a mfc dialog programe in vs.net 2003 use vc++ . > In the dialog I add a datagrid(sp4,com) and a adodc(sp5,com) but when I > close the dialog ,it give me a Error,why?(I had set datagrid's datafield) >...

Problem with Exchange
Hi, I have an estange problem with the Exchange Server of my company. I have done many tests, but I can't get a solution The environment is a domain, controlled by several WinNT Servers, i.e, one PDC and 5 BDC's. A server member, Windows 2000, is the machine that manages the mail, by means of an Exchange Server 5.5 I have an user that connects to the server from a machine with Windows XP and Office 2003, using Outlook 2003 to get his mail. That user (for example, proldan) has been receiving and sending mail in his mailbox during 10 days, (since he begun to work in the company)...

problems with imap server
i have been able to read and access my imap folder and read all new mail until just recently. now i keep getting the following message, "cannot open set of folders. server is unavailable." does anyone know if this is a common problem and how can i resolve it??? please help if possible. ...

Problem with Hyperlinks in Excel 2003
Recently my company has moved to office professional 2003. Since the upgrade, I have experience 2 PC=E2=80=99s with the same issue. That is, opening any new excel file, adding say http://www.microsoft.com to one of the cells and then trying to click the hyperlink to view the http content. Every attempt generates the following error. =E2=80=9CCannot locate the internet server or proxy server.=E2=80=9D Attempts to resolve this issue are: Log on as a different user to determine if it=E2=80=99s a profile problem.. =EF=83=A0 Other accounts generate the same error. Determine if error is isolated to...

Unknown Recipient Problem
Hi All, ExchServer2000 When unknown recipient e-mail hits one of my customers, they automaitcally go to a mailbox called ian@domainname.co.uk. Where do I change it so that they go to the Administrator account - I've looked everywhere and I can't even find where it says to direct to ian's mailbox. Any/All Help Very Much Appreciated TIA Mark Hello Mark, check the setting "send copy of non-delivery report to" on the virtual SMTP server of the E2K server ... Or check if the mailbox in question has an e-mail address named "postmaster@<primary SMTP domain ...

Real One Player for PocketPC problem
I downloaded Real One Player for Pocket PC to my Dell Axim X3i, but it doesn't seem to work. Every Real Audio and Real Video clip I click on results in an error message. Sometimes it says "Cannot play this clip." Sometimes it asks me to downlod the file first and then open it... but when I do that, it always times out with the server. I transferred an audio clip from my PC to the Axim, but that wouldn't play either. (I notice the Real One Player web site has a note saying transferred clips will only play on a HP iPAQ.) Has anyone else had a similar problem? ...

Compounding Formula problems
Does Anyone know a formula for Calculating monthly compounding and Semi annual compounding, but not in advance. Thank you Ryan goto help>contents>functions>financial functions> -- Don Guillett SalesAid Software donaldb@281.com "Ryan Sheepwash" <RyanSheepwash@discussions.microsoft.com> wrote in message news:7E435AB3-3A18-4F16-A254-3BFAE1030B01@microsoft.com... > Does Anyone know a formula for Calculating monthly compounding and Semi > annual compounding, but not in advance. > > Thank you > Ryan Are you looking for the monthly payment? If so,...

Transaction problem between DAO and ADO
Hi, Was using DAO all along and following code was working fine: wrkspc.Begintrans with rst ..MoveFirst Do Until .EOF ..AddNew !pkID = DMax("pkID", "Table") +1 ....other table fields ..Update ..MoveNext Loop End with User Confirmation... wrkspc.committrans User Cancellation wrkspc.rollback Have recently switched to ADO - not much experience with it either... Reworded the above code to make it suitable for ADO. Using connection object to begin/commit/rollback the transaction. Problem is that the code raises an error in ADO because the DMax fu...

OPOS SO LineDisplay CreateWindow problem
Hi, How can I solve this problem: MS VisualStudio C++ thinks that CreateWindow is a macro and tries to expand it. It seems that this is a well-known problem, however I have not found any applicable solution. -Esko Correct, this is a macro defined to CreateWindowA or CreateWindowW Why is this bad? -- Maxim Shatskih, Windows DDK MVP StorageCraft Corporation maxim@storagecraft.com http://www.storagecraft.com "eskomk" <esko.hujanen@gmail.com> wrote in message news:0877a2c3-f20b-484f-bc0b-ae5f3929bf4c@e53g2000hsa.googlegroups.com... > Hi, > > How can I solve...

Styles and Set Numbering Value problem
Running MS Office Pro 2007 with Windows XP Pro. I have styles defined for numbered lists. These styles have simple characteristics, like indent, space before, etc. - nothing special. I want the numbered lists to appear throughout the document, with each starting at the number 1. When I try to start a new list using the Set Numbering Value dialog, I get a message saying "Changing the number format for this list updates the style "NumList" and reapplies the style to each paragraph. Do you want to continue?" If I select no, the starting number is not chan...

Ex5.5 IS
Hello! I have a problem with an Exchange 5.5 server: The Information Store stopped with the logged event 1160, Source: Exchange IS, Database resource failure error Out of memory occurred in function JTAB_BASE::EcCreateIndex while accessing the database. It starts with that when users sort their mail in an folder, they get invisible... Users also use PGP... I noticed that there are two KB articles that describe this event. But according to these articles this is only a problem with earlier SPs but we use SP4 on that server. The version of store.exe is: 5.5.2657.74 and from Perfiz.ex...

double axis, double problem (i hope only to me)
Hi everyone! I`ve got this small problem about charting. Data entry has to be located on y-axis and after calculations, it ends up with 2 different values. Both of them are always positive and must be located on x-axis. What I must to do is to present first value on the left side of y-axis and second one on the right side, see link below: http://img301.imageshack.us/img301/3594/1980id7.png Is there any way to make it possible so that chart looks something similar to this one in the linked pic? Thanks and sorry about bad English. Cheers... Your picture looks like a tornado chart: http:/...

License Problem 08-25-06
Hi, We have been running Microsoft CRM 3.0 SBE on SBS 2003. We have purchased CRM 3.0 Professional and intend to redeploy CRM on a Windows 2003 Standard Server on a new Domain. The problem I have experienced, is that after running the redeploy wizard succesfully. While installing CRM I receive the error "You cannot mix SBE and Professional License Keys.". We no longer wish to use the SBE keys, so if there is a way I can remove them as part of the redeploy this would be fine. How do I avoid this? "Joeh" wrote: > Hi, > We have been running Microsoft CRM 3.0 ...

Sort by Name Problem
Hi, I'm using Outlook 2003, SP3 with Exchange server. When I sort my Inbox by name, I highlight a name (ie, John Doe) then click the "From" column heading. Sometimes the cursor will land on the name John Doe, which is what I want, and other times the cursor will land on the first alphabetical name in my Inbox, and I then have to scroll down to find John Doe's email. Is this a known issue and is there a solution? Thanks. -- Carolyn ...

External messages problem
Hi all, I have this problem with a customer, they have an Exchange 2003 Server (SBS2003). The external mailboxes resides on the ISP so they download messages to internal mailboxes through POP3 connector. This works fine but we see 2 problems: 1) When an internal user wants to send messages to an external user the mail is sended with the "reply-to" pointing to the internal mailbox (not resolvable from internet), so when the external user wants to reply and sends the email the address is not recongnized and the email bounces. 2) If we change the "reply-to" to match the...

Sumproduct with multiple text criteria
I have seen this question answered a couple times but none fit my situation. I am trying to use the following formula: =SUMPRODUCT(--(AL2:AL50000="*GGF*"),--(AD2:AD50000="Charleston")) to search two columns of text and return the count for the rows that meet both criteria. (If AL2 has the text GGF somewhere in the cells text string and AD2 has the text Charleston in its cell, then it would count 1.) It is not giving me the count that is correct, it is returning a zero. My problem is that the text string in column AL contains different strings of text, and I only wan...

Problems with GAL so weird!!!
I have 6 exchange servers in my Exchange org and one of them has a problem with its GAL, all other servers do not experience this problem? Here goes.... When going to send a mail on the affected server, you click the To: button and select the names you want to include in the e-mail. everything works fine however if you select an address surname begining with the letter M it goes to the bottom of the GAL? any other name is ok.all other servers do not experience this problem? It just seems to be the addresses starting with the letter M? does this mean my dir.edb is corrupt? This is really co...

help! : problem with FORMAT function
i've genned an application using Access 97 (on Windows XP). when i port this to a windows 97 machine, i get a compile error on the "Format" function - "library not found". any suggestions ? Press Ctrl+G to open the Immediate Window. Choose References from the Tools menu. Anything marked "MISSING"? More info on references: http://members.iinet.net.au/~allenbrowne/ser-38.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. &q...

Undo
Good morning; Excel 2007 This command (Undo - Redo) is not working. Is there a control that needs to be turned on? Thanks in advance John You do know that certain actions are no 'undo-able'? Type the letter A in a cell; press Enter; now hold down CTRL and press Z. Did the A disappear? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John" <jddonadio@bellsouth.net> wrote in message news:KOl4m.20016$Xl4.2566@bignews5.bellsouth.net... > Good morning; > > Excel 2007 > > This command (Undo ...

Problem with displaying characters in excel sheet
I an excel sheet, I am displaying a character alpha "?". But when I retrieve this chaarcter though a messagebox function in VB editor, say msgbox mysheet.cells(4,5) then it shows "a" and not alpha... What am I missing ? TIA. How a character displays is depenent on the font used to display it. Perhaps you want to use a Userform/textbox or label to display it since that would allow you to specify a font to display it with. -- Regards, Tom Ogilvy "Noee" <anonymous@discussions.com> wrote in message news:uipdIwRsEHA.2688@TK2MSFTNGP14.phx.gbl... > I an...

problem in UI thread
Hi I'm opening a UI Thread inside a dll method. i wanted to get the value returned by the thread. so i call getexitcodethread with the handle of hte thread but i get an error code 6 (invalid handle). though the hanle value passed to GetExitCodeThread() is the same as i get from the CWinThread->m_hThread returned by AfxBeginThread(); Can somebody tell me what could be wrong. here is the code snippet CMyThread * myThread =(CMyThread *)AfxBeginThread(RUNTIME_CLASS(CMyThread), THREAD_PRIORITY_NORMAL, 0, // stack size CREATE_SUSPENDED); if(myThread != NULL) ...

"The operation failed due to network or other communication problems.
"The operation failed due to network or other communication problems. Check your connections and try again" I'm getting this error message on a number of my computers. It will display part of the screen in the top left corner with this message. When i click ok it continues to open the email without any problems. I had found a solution that requires me to removed some Registry Entrys and then do a Detect and Repair on Microsoft Outlook which hasen't solved the problem. Does anyone have any ideas for me? Windows XP Professional Microsoft Office 2000 w/ SP3 Internet Mail a...