SUMPRODUCT 05-06-10

Hello,

I've used this formular to find work piece that gets done per department.
=SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532"))

Department 14532 was not picked up even though other department's number was 
correctly counted. But if I put a letter, say like C (14532C) and adds C to 
my formular as in 
=SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it picks 
up how many pieces were done for this department.

I suspect this is in my cell format but I could not figure out what went 
wrong. Please help. Thanks.

Tmt
0
Utf
5/6/2010 9:35:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
741 Views

Similar Articles

[PageSpeed] 35

try withOUT the "    "

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Tmt" <Tmt@discussions.microsoft.com> wrote in message 
news:2E3CDDFC-848F-47C8-9DFE-531CD6DE94AD@microsoft.com...
> Hello,
>
> I've used this formular to find work piece that gets done per department.
> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532"))
>
> Department 14532 was not picked up even though other department's number 
> was
> correctly counted. But if I put a letter, say like C (14532C) and adds C 
> to
> my formular as in
> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it picks
> up how many pieces were done for this department.
>
> I suspect this is in my cell format but I could not figure out what went
> wrong. Please help. Thanks.
>
> Tmt 

0
Don
5/6/2010 9:53:18 PM
Don,

Thanks for the suggestion. Dropping the " " for those problematic set of 
data makes those items counted for. 

But here's another problem. I also discovered that this same formula without 
the " " would not read other set of data or miscounted some other set of data 
(4 items done from Q2 data sheet only returned as 3 items done). How do I 
unify all so that one formula works without keeping the " " and not for the 
others?

Thanks.

Tmt 

"Tmt" wrote:

> Hello,
> 
> I've used this formular to find work piece that gets done per department.
> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532"))
> 
> Department 14532 was not picked up even though other department's number was 
> correctly counted. But if I put a letter, say like C (14532C) and adds C to 
> my formular as in 
> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it picks 
> up how many pieces were done for this department.
> 
> I suspect this is in my cell format but I could not figure out what went 
> wrong. Please help. Thanks.
> 
> Tmt
0
Utf
5/7/2010 5:07:01 PM
Perhaps some "numbers" are text and some are numbers. Change all to numbers.
Sub fixmynums()
Application.ScreenUpdating = False
'lr = Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
    For Each c In Selection 'Range("a1:q" & lr)
   If Trim(Len(c)) > 0 And c.HasFormula = False Then
    c.NumberFormat = "General"
    c.Value = CDbl(c)
   End If
  Next

Application.ScreenUpdating = True
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Tmt" <Tmt@discussions.microsoft.com> wrote in message 
news:4395DDCE-EF55-4571-A372-842BE8F55649@microsoft.com...
> Don,
>
> Thanks for the suggestion. Dropping the " " for those problematic set of
> data makes those items counted for.
>
> But here's another problem. I also discovered that this same formula 
> without
> the " " would not read other set of data or miscounted some other set of 
> data
> (4 items done from Q2 data sheet only returned as 3 items done). How do I
> unify all so that one formula works without keeping the " " and not for 
> the
> others?
>
> Thanks.
>
> Tmt
>
> "Tmt" wrote:
>
>> Hello,
>>
>> I've used this formular to find work piece that gets done per department.
>> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532"))
>>
>> Department 14532 was not picked up even though other department's number 
>> was
>> correctly counted. But if I put a letter, say like C (14532C) and adds C 
>> to
>> my formular as in
>> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it 
>> picks
>> up how many pieces were done for this department.
>>
>> I suspect this is in my cell format but I could not figure out what went
>> wrong. Please help. Thanks.
>>
>> Tmt 

0
Don
5/8/2010 12:12:18 PM
Reply:

Similar Artilces:

Work Hours 10-25-06
Hi there, I want to change the default work hours setting for users of the system. At the moment they are set to work all day everyday of the week. Realistically I want this to be 9 a.m. to 5 p.m. Monday to Friday. Is there any way I can change this using customization and have it apply to all users? Pergaps it is a system setting I can change rather than customize? Many thanks, Mark Mark, Go to Settings | Business Unit Settings | User and select a user (can also go Service calander and then select the user from there as well) Then view the Work Hours, and select Set Up | New Weekly S...

deleted emails 03-10-10
how do i retrieve deleted emails from two specific email addresses? sort the deleted items folder on FROM address "blmc" wrote: > how do i retrieve deleted emails from two specific email addresses? ...

IE8 text size 01-25-10
The situation described occurs in IE8 on both Vista home premium and Windows 7 professional. Using the page options, I set the text size to smaller. It will remain that way for a while but within hours or sometimes a day the text size reverts to medium. Any suggestions Lou ...

Can't send or receive 07-27-10
I set up Windows Mail to my gmail account. I've double checked the settings, and they are correct. I got the following message when I tried to send an e-mail: The message could not be sent. The authentication setting might not be correct for your outgoing e-mail [SMTP] server. For help solving this problem, go to Help, search for "Troubleshoot Windows Mail", and read the "I'm having problems sending e-mail" section. If you need help determining the proper server settings, please contact your e-mail service provider. The rejected e-mail address ...

List output to a x by 10 range
I have a list that x lines long. I want to transpose that list to another range (starting on cell M1) that is 10 columns wide and however many rows long. I know this is super simple. Does it have something to do with range resizing? No doubt you've already experimented with macro recorder, discovering the standard transpose command wont wrap within a range. So we need handle the transposition manually. Presuming your data begins in cell A2: Sub test() For Each c In Range("A2", "A" & Range("A65536").End(xlUp).Row) Row = Row + 1 ...

Business Portal 4.0 & GP 10.0 Smartlist
Hi, Is there a way to use our GP 10 smartlists in the query sections of Business Portal 4.0 ? I've look at the Business Portal query builder and I don't think you can use SQL (in this case I could have extract Smartlists SQL). You have to used predefined entities. Maybe I get it wrong ? Thanks in advance for any help ! Richard, Thanks for using the newsgroups. You are correct. You need an entity for querying in Business Portal. Thanks Rob -- Rob Bernhardt Software Quality Assurance Microsoft Business Solutions This posting is provided "AS IS" with no warranties...

Filter #10
Hi We have the following code that filters a range in database(no problem) this code is looking at column A which is a week number(11) what we now need to do is extend this code to filter two ranges the second range is column H and is days of the week(Monday) Is it possible to expand this code to do this? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then Sheets("data2").Range("Criteria").Calculate Worksheets("data2").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=She...

Outlook security #10
Hi, I have Office 2003 with Outlook 2003 running on Windows 2000. When sending emails from a different program, I can set Outlook security to bypass further warnings for up to 10 minutes. A customer, also with Outlook 2003, but updated from 2002, has to respond to every email with a 5 second pause. My questions: Why the different behaviour? How do I change the latter behaviour to the former? Thanks Thomas Hi Thomas, The security settings for Outlook 2003 can be found using the Tools->Options->Security Tab and security zone settings button. It works exactly the same way Internet ...

Office 2010 Beta 02-24-10
I have installed 2010 Beta and like it but I no longer can click on a file to open. When I try to associate the program to use none of the office programs show up. Is there a way aroung this? Microsoft Office Diagnostics perhaps, Or select Browse and look for the exe file. thom kamp wrote: > I have installed 2010 Beta and like it but I no longer can click on a file to > open. When I try to associate the program to use none of the office programs > show up. Is there a way aroung this? ...

large sumproducts causing memory errors
Has anyone experienced problems while using sumproducts which cover large arrays, lets say a few thousand lines, in an exterior workbook? More specifically, I'm using sumproduct in spreadsheet 1... =sumproduct(--([array in spdsht 2]=[reference in spdsht 1]),--([same as first ex. for another qualifier]),--([sum array in spdsht 2])) Formula works correctly when spdsht2 is open. To my understanding(and some experience) the sumproduct link should be maintained with spreadsheets that are closed. When opening spreadsheet 1(without opening spreadsheet 2), I receive an error which says,...

integration manager 10.0-SQL Data Source
We are trying to run an integration by using a link to the GP tables. One of our data sources is using the SQL Server as the data source. We can query the data source and return all rows in the integration. However, when we run the integration we receive the error: An error handler was not found for: Object reference not set to an instance of an object. We are on the latest service pack too. Are you using any field script or event script in IM? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogst...

JavaScript Error 07-06-05
We just installed the CRM to evaluate it and we are getting the JavaScript error "Permission Denied" on the bottom toobar. It works with the Windows 2000 machines, but not our Win XP SP2 machines? Is there some sort of security setting we need to set? Any help would be appreciated. For anyone else who is looking I finally found it on MSDN. http://support.microsoft.com/default.aspx?scid=kb;en-us;870635 "Brian Linden" <brian@ineedadip.com> wrote in message news:uauICYlgFHA.4032@TK2MSFTNGP10.phx.gbl... > We just installed the CRM to evaluate it and we are g...

windows media player 02-07-10
when trying to play a dvd this message appears. windows media player cannot dvd video. I have installed decoders. How can i get it to play my dvds On Sat, 6 Feb 2010 16:29:01 -0800, lmorris <lmorris@discussions.microsoft.com> wrote: >when trying to play a dvd this message appears. >windows media player cannot dvd video. >I have installed decoders. >How can i get it to play my dvds Do you have suitable DVD decoders ? Only a subset are media center compatible (the feature you need to look out for) DecCheck for XP, and the Vista VMCD utility can help here : ...

Multiple Commas 02-11-10
Some people decided to play a cruel joke and pulled two commas into a name field and i need to split the name into a first and last name column. Normally i could do this but the second comma is throwing me off. It looks like this: last name,first name, Ive tried doing a replace on the last comma but its not working. Does anyone know any tricks on how to either get rid of that second comma or split the field into two seperate columns as it is? Thanks for any help. dim aName() as string dim strName as string dim strLastName as string dim strFirstName as string ' just ...

PRODUCT KEY 04-05-10
I've just purchased this hp and the cd that was provided to me with the product key sticker on front does not work. I was under the impression that there would be some type of trial period that would allow me to use the product to determine if I want it or not. I am unable to open any documents in word on my cpu because this software has somehow locked me out of my papers for school. I can't edit copy or even view the docs w/o the product key. The product key provided is 7X4P3 Q24MX KHRKH R6CX2 G76Y4 it clearly doesn't work rather it is in lower case or upper case for...

Can't receive mail 07-29-10
I have problems receiving mail. I've worked through the trouble shooting guidelines and nothing works. I get a Windows Security box requesting user name and password but entering the details doesn't fix the problem The strange thing is that sometimes after I have gone in and reentered details in the Accounts Properties - Server tab it will down load them BUT NOT EVERY TIME. The Error message below is the most common one that I get. Account: 'mail.bigpond.com', Server: 'mail.bigpond.com', Protocol: POP3, Server Response: '-ERR [IN-USE] account is...

Stats microsoft.public.windowsxp.general (last 7 days) 07-05-10
"Caveat: Quantity is not necessarily a measure of Quality" Newsgroup.................: microsoft.public.windowsxp.general Stats Were Taken..........: Mon, 05 Jul 2010 08:15:20 GMT Stats Begin...............: Mon, 28 Jun 2010 08:26:44 GMT Stats End.................: Mon, 05 Jul 2010 07:03:30 GMT Days......................: 7 Total No. of Articles.....: 606 Total No. of Characters...: 1707259 Total Volume..............: 1667 Messages Per Day..........: 86.6 Characters Per Day........: 243894.1 Average Daily Volume......: 238 kB Total Posters This Week...:...

Word spell check 04-21-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How do I skip all repeated words. My documents intentionally use repeat words but the spell checker always stops at each one. The ignore all and the add functions are not available The spelling checker will always stop at repeated words. Your only option is not to use Spelling. Cheers On 21/04/10 4:34 PM, in article 59bb736f.-1@webcrossing.JaKIaxP2ac0, "amclean4@officeformac.com" <amclean4@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard...

MSDN 10 user license
As we're setting up our development and test environments for CRM, we're trying to determine which licenses are best to use. I know we can use the MSDN 10 user license, and that won't prompt me to register within 30 days. Is that time limited (e.g. expires after 30 days or 90 days)? We want true development and test environments where we can write our customizations before moving them into production, so don't want to be restricted to a time period. Thanks Ian The MSDN and Action Pack Licenses are "included" with your yearly subscription and hence they do not exp...

GP 10 Analysis cubes and AA ?
Hello, Has someone used the new Analysis Cubes for GP 10 provided by Microsoft, and if yes do they come with standard cubes covering Analytical Accounting (AA) transactions ? If not, are you aware of a third party selling this ? Thanks Antoine AA is not part of the analysis cubes. The cubes can be modified to include them. Modifying them will require somebody who has that skill set. -- Charles Allen, MVP "aleblanc" wrote: > Hello, > > Has someone used the new Analysis Cubes for GP 10 provided by > Microsoft, and if yes do they come with standard cubes covering...

GP 10.0 Reporting...
Anyone know the latest news on GP 10.0 and any details related to the replacement of Report Writer? There has been quite a bit of talk about SQL Reporting Services, or even Excel becoming the main reporting engine in GP 10.0. We'd love to hear something definitive on the subject as 10.0 is right around the corner. Thanks...Ken All I have is from a presentation a colleague saw. V10 will have 150+ Excel-based reports and 100+ SQL Server Reporting Services reports. -- Charles Allen, MVP "Kenneth J. Papucci" wrote: > Anyone know the latest news on GP 10.0 and a...

HELP! Extender objects gone after SP4 (10.00.1400)
I upgraded to SP4 10.00.1400 and all my Extender objects dissapeared including Windows, Views, Inquiries, etc. Where did they go? Help! thanks, -- Hector Herrera Business Systems Analyst II Northwestern Medical Faculty Foundation Chicago, IL USA There is a link to a hot topic on the SP4 download page. https://mbs.microsoft.com/customersource/support/selfsupport/hottopics/HOT_TOPIC_MDGP10_ExtenderSP4Issues -- Charles Allen, MVP "Hector Herrera" wrote: > I upgraded to SP4 10.00.1400 and all my Extender objects dissapeared > including Windows, ...

Customization 06-23-06
Hi, I have a CRM 1.2 version, i need make some modifications: 1. Sum 2 fields(textbox's) and show in another textbox in the oportunnity form. 2. change textbox field "Probability" in Read-only status. How i do this???? Please, help me... a friend said, "create a Calculated fields", How i do this?? or what is the real solution for this problem?? CRM 1.2 only supported client side code for the onchange event in picklists. You could sum the two fields behind the scenes using a callout. ======================= John O'Donnell Microsoft CRM MVP http://codegallery....

Print merge stops after 10 pages in Pub2003
I am trying to do some very large merges in Publisher, and queue the result to Adobe PDF. (and yes, I need Publisher for aspect of the document rather than Word.) When I use "print merge" it prints rapidly, but only prints 10 pages to the named file, and then prompts for a different file for each 10 pages. I can repeat the behavior on Windows XP Pro, Windows XP Media Ctr and on a Windows 2003 server. I can do a mail/catalog merge to another Publisher file, and then print it to Adobe PDF, but it takes FOREVER to print to PDF. WHY does print merge quit after 10 sheets? Ther...

how do you change the print merge batch size from 10 records?
How can I change the default output of 10 printed records to say 500? Chris Bartlett <ChrisBartlett@discussions.microsoft.com> was very recently heard to utter: > How can I change the default output of 10 printed records to say 500? http://support.microsoft.com/default.aspx?scid=kb;en-us;891904 has details of how to change the batch size to 1 for stapling/collating purposes. If you follow the instructions, but use a value of 500 instead of 1, then you will get 500 records per batch. -- Ed Bennett - MVP Microsoft Publisher ...