#### SUMIF question (although, I think I'm using the wrong function)

```I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917	4
174327	6
171629	9
174327	13
172832	5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the example
above, for 174327, it would equal 19. (13+6).

```
 0
Utf
1/15/2010 5:32:01 PM
excel.misc 78881 articles. 5 followers.

8 Replies
790 Views

Similar Articles

[PageSpeed] 7

```=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))
--
Gary''s Student - gsnu200909

"Ashley" wrote:

> I have a 2 column log that tracks incoming call amounts from agents
> identified as 6 digit numbers CCOOAA - County, Office, Agent#:
>
> 171917	4
> 174327	6
> 171629	9
> 174327	13
> 172832	5
>
> I want to add another table that takes ONLY the first 4 digits from the 6
> digit number, then adds the totals from their call amounts. IN the example
> above, for 174327, it would equal 19. (13+6).
>
>
```
 0
Utf
1/15/2010 5:42:01 PM
```You can do this with the SUMPRODUCT function. Something like:

=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

The double negative (--) is in there to change the text output from the LEFT
function back into a number value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Ashley" wrote:

> I have a 2 column log that tracks incoming call amounts from agents
> identified as 6 digit numbers CCOOAA - County, Office, Agent#:
>
> 171917	4
> 174327	6
> 171629	9
> 174327	13
> 172832	5
>
> I want to add another table that takes ONLY the first 4 digits from the 6
> digit number, then adds the totals from their call amounts. IN the example
> above, for 174327, it would equal 19. (13+6).
>
>
```
 0
Utf
1/15/2010 5:44:04 PM
```"Luke M" wrote:
> =SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

You seem to have several typos.

> The double negative (--) is in there to change the text output
> from the LEFT function back into a number value.

If that was your intent, you need the double negation to be adjacent to the
LEFT function, thus:

=SUMPRODUCT((--LEFT(A2:A100)=1743)*(B2:B100))

The also remedies your syntax error.

But your formula still does not make sense since the default length for LEFT
is 1 character.  Perhaps you meant:

=SUMPRODUCT((--LEFT(A2:A100,4)=1743)*(B2:B100))

But why not write, more simply:

=SUMPRODUCT((LEFT(A2:A100,4)="1743")*(B2:B100))

with or without the parentheses around B2:B100.

Still, I would prefer:

=SUMPRODUCT(--(LEFT(A2:A100,4)="1743"), B2:B100)

since that avoids #VALUE errors if some of B2:B100 are non-numeric

----- original message -----

"Luke M" wrote:
> You can do this with the SUMPRODUCT function. Something like:
>
> =SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))
>
> The double negative (--) is in there to change the text output from the LEFT
> function back into a number value.
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Ashley" wrote:
>
> > I have a 2 column log that tracks incoming call amounts from agents
> > identified as 6 digit numbers CCOOAA - County, Office, Agent#:
> >
> > 171917	4
> > 174327	6
> > 171629	9
> > 174327	13
> > 172832	5
> >
> > I want to add another table that takes ONLY the first 4 digits from the 6
> > digit number, then adds the totals from their call amounts. IN the example
> > above, for 174327, it would equal 19. (13+6).
```
 0
Utf
1/15/2010 6:25:01 PM
```Sorry to be pedantic but the double negation converts Boolean (FALSE/TRUE)
to numbers (0/1), it is not converting text. See
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Luke M" <LukeM@discussions.microsoft.com> wrote in message
news:1FE6ED0D-DFA2-408B-A020-4C94F410C516@microsoft.com...
> You can do this with the SUMPRODUCT function. Something like:
>
> =SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))
>
> The double negative (--) is in there to change the text output from the
> LEFT
> function back into a number value.
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Ashley" wrote:
>
>> I have a 2 column log that tracks incoming call amounts from agents
>> identified as 6 digit numbers CCOOAA - County, Office, Agent#:
>>
>> 171917 4
>> 174327 6
>> 171629 9
>> 174327 13
>> 172832 5
>>
>> I want to add another table that takes ONLY the first 4 digits from the 6
>> digit number, then adds the totals from their call amounts. IN the
>> example
>> above, for 174327, it would equal 19. (13+6).
>>
>>
```
 0
Bernard
1/15/2010 6:26:47 PM
```Thanks Joe. Guess this is what happens the closer I get to the weekend...
Have a good one.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Joe User" wrote:

> "Luke M" wrote:
> > =SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))
>
> You seem to have several typos.
>
>
> > The double negative (--) is in there to change the text output
> > from the LEFT function back into a number value.
>
> If that was your intent, you need the double negation to be adjacent to the
> LEFT function, thus:
>
> =SUMPRODUCT((--LEFT(A2:A100)=1743)*(B2:B100))
>
> The also remedies your syntax error.
>
> But your formula still does not make sense since the default length for LEFT
> is 1 character.  Perhaps you meant:
>
> =SUMPRODUCT((--LEFT(A2:A100,4)=1743)*(B2:B100))
>
> But why not write, more simply:
>
> =SUMPRODUCT((LEFT(A2:A100,4)="1743")*(B2:B100))
>
> with or without the parentheses around B2:B100.
>
> Still, I would prefer:
>
> =SUMPRODUCT(--(LEFT(A2:A100,4)="1743"), B2:B100)
>
> since that avoids #VALUE errors if some of B2:B100 are non-numeric
>
>
> ----- original message -----
>
> "Luke M" wrote:
> > You can do this with the SUMPRODUCT function. Something like:
> >
> > =SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))
> >
> > The double negative (--) is in there to change the text output from the LEFT
> > function back into a number value.
> > --
> > Best Regards,
> >
> > Luke M
> > *Remember to click "yes" if this post helped you!*
> >
> >
> > "Ashley" wrote:
> >
> > > I have a 2 column log that tracks incoming call amounts from agents
> > > identified as 6 digit numbers CCOOAA - County, Office, Agent#:
> > >
> > > 171917	4
> > > 174327	6
> > > 171629	9
> > > 174327	13
> > > 172832	5
> > >
> > > I want to add another table that takes ONLY the first 4 digits from the 6
> > > digit number, then adds the totals from their call amounts. IN the example
> > > above, for 174327, it would equal 19. (13+6).
```
 0
Utf
1/15/2010 6:31:01 PM
```"Gary''s Student" wrote:
> =SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))

The double negation before B1:B5 might be useful if you assume B1:B5
contains numeric strings (text).  But in that case I would prefer the simpler
form:

=SUMPRODUCT((LEFT(A1:A5,4)="1743")*(B1:B5))

with or without the parentheses around B1:B5.

Otherwise, the double negation before B1:B5 seems superfluous, as are the
parentheses.  More to the point, --B1:B5 defeats the purpose of writing
separate SUMPRODUCT arguments.

If B1:B5 is expected to contain numbers, I would prefer:

=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"), B1:B5)

since that avoids a #VALUE if some of B1:B5 are purposely non-numeric and to
be ignored.

----- original message -----

"Gary''s Student" wrote:

> =SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))
> --
> Gary''s Student - gsnu200909
>
>
> "Ashley" wrote:
>
> > I have a 2 column log that tracks incoming call amounts from agents
> > identified as 6 digit numbers CCOOAA - County, Office, Agent#:
> >
> > 171917	4
> > 174327	6
> > 171629	9
> > 174327	13
> > 172832	5
> >
> > I want to add another table that takes ONLY the first 4 digits from the 6
> > digit number, then adds the totals from their call amounts. IN the example
> > above, for 174327, it would equal 19. (13+6).
```
 0
Utf
1/15/2010 6:33:01 PM
```"Luke M" <LukeM@discussions.microsoft.com> wrote:
> Thanks Joe. Guess this is what happens the closer I get to the weekend.

Been there, done that! :-)

> Have a good one.

I certainly will, since I will finally return home to a less stressful
environment.

----- original mail -----

"Luke M" <LukeM@discussions.microsoft.com> wrote in message
> Thanks Joe. Guess this is what happens the closer I get to the weekend...
> Have a good one.
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Joe User" wrote:
>
>> "Luke M" wrote:
>> > =SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))
>>
>> You seem to have several typos.
>>
>>
>> > The double negative (--) is in there to change the text output
>> > from the LEFT function back into a number value.
>>
>> If that was your intent, you need the double negation to be adjacent to
>> the
>> LEFT function, thus:
>>
>> =SUMPRODUCT((--LEFT(A2:A100)=1743)*(B2:B100))
>>
>> The also remedies your syntax error.
>>
>> But your formula still does not make sense since the default length for
>> LEFT
>> is 1 character.  Perhaps you meant:
>>
>> =SUMPRODUCT((--LEFT(A2:A100,4)=1743)*(B2:B100))
>>
>> But why not write, more simply:
>>
>> =SUMPRODUCT((LEFT(A2:A100,4)="1743")*(B2:B100))
>>
>> with or without the parentheses around B2:B100.
>>
>> Still, I would prefer:
>>
>> =SUMPRODUCT(--(LEFT(A2:A100,4)="1743"), B2:B100)
>>
>> since that avoids #VALUE errors if some of B2:B100 are non-numeric
>>
>>
>> ----- original message -----
>>
>> "Luke M" wrote:
>> > You can do this with the SUMPRODUCT function. Something like:
>> >
>> > =SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))
>> >
>> > The double negative (--) is in there to change the text output from the
>> > LEFT
>> > function back into a number value.
>> > --
>> > Best Regards,
>> >
>> > Luke M
>> > *Remember to click "yes" if this post helped you!*
>> >
>> >
>> > "Ashley" wrote:
>> >
>> > > I have a 2 column log that tracks incoming call amounts from agents
>> > > identified as 6 digit numbers CCOOAA - County, Office, Agent#:
>> > >
>> > > 171917 4
>> > > 174327 6
>> > > 171629 9
>> > > 174327 13
>> > > 172832 5
>> > >
>> > > I want to add another table that takes ONLY the first 4 digits from
>> > > the 6
>> > > digit number, then adds the totals from their call amounts. IN the
>> > > example
>> > > above, for 174327, it would equal 19. (13+6).

```
 0
Joe
1/15/2010 6:42:18 PM
```Your comments are very clear and informative.

--
Gary''s Student - gsnu200909

"Joe User" wrote:

> "Gary''s Student" wrote:
> > =SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))
>
> The double negation before B1:B5 might be useful if you assume B1:B5
> contains numeric strings (text).  But in that case I would prefer the simpler
> form:
>
> =SUMPRODUCT((LEFT(A1:A5,4)="1743")*(B1:B5))
>
> with or without the parentheses around B1:B5.
>
> Otherwise, the double negation before B1:B5 seems superfluous, as are the
> parentheses.  More to the point, --B1:B5 defeats the purpose of writing
> separate SUMPRODUCT arguments.
>
> If B1:B5 is expected to contain numbers, I would prefer:
>
> =SUMPRODUCT(--(LEFT(A1:A5,4)="1743"), B1:B5)
>
> since that avoids a #VALUE if some of B1:B5 are purposely non-numeric and to
> be ignored.
>
>
> ----- original message -----
>
> "Gary''s Student" wrote:
>
> > =SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))
> > --
> > Gary''s Student - gsnu200909
> >
> >
> > "Ashley" wrote:
> >
> > > I have a 2 column log that tracks incoming call amounts from agents
> > > identified as 6 digit numbers CCOOAA - County, Office, Agent#:
> > >
> > > 171917	4
> > > 174327	6
> > > 171629	9
> > > 174327	13
> > > 172832	5
> > >
> > > I want to add another table that takes ONLY the first 4 digits from the 6
> > > digit number, then adds the totals from their call amounts. IN the example
> > > above, for 174327, it would equal 19. (13+6).
```
 0
Utf
1/15/2010 6:46:02 PM

Similar Artilces:

*major* functional issue with MOSS/WSS?
We are looking to move a big site over to using MOSS in a big as an alternative to shared network drives (ugh). One of the biggest issues is sending docs as attachments via email for others to edit. We'd like to get everyone to use the 'send a link' functionality from thew Sharepoint DL BUT when the user receives the email and clinks on the embedded link the doc opens read only. They then cant edit and save their changes. Seems a big an counter intuitive functional flaw. Are we missing something here? Is there any easy (for the end user) way to send those links to a doc ...

Concatenation question
I am trying to concatenate two fields and then add text in brackets to the end. Here is what I have: StaffFullName = ([Me.LastName] & ", " & [Me.FirstName] & " (Mgr)") I want the end result to be: Smith, Mary (Mgr) How do I achieve this? Thank you, Terri What you've done will work. You can also use the "+" operator if there is a possibility that there will be a missing first name: StaffFullName = ([Me.LastName] & "," & (" "+ [Me.FirstName]) & " (Mgr)") That will avoid an extra spa...

100% stacked column chart shows wrong information
I wonder if someone can help me. I have created a table that tracks the profitability of a particular project and I’m using the table as a source for a 100% Stacked Column graph. The source data for the graph is in three rows: • B21:M21 - contain the names of the months from Nov – Oct • B22:M22 - money taken in • B24:M24 - profit B23:M23 contains the money paid to supplier each month. I need this in the table but I don’t need to show it in the graph. I just want to show profit as a proportion of what we bill to customers on that project. My problem is that the graph seems to show in...

fiscal year setting question
hi. i know that the fiscal year settings get more or less set in stone, so i had a quick question before i set it for my org. in the template field, what is that used for? if i select 'quarterly', does that mean i can only run reports on a quarter basis? what if i wanted to do monthly reports? ultimately, my question is what is the 'template' field used for? thanks! Hi Jeff, Fiscal year setting must be aligned to your company's accountig policy. Fiscal year setting primarily defines sales quotas. When you set quarterly periods, you can set quarterly quotas for mem...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Outlook 2007 Install gone wrong
We recently switched e-mail hosts and I successfully (mostly) switched over my company's accounts from a POP server to our new IMAP server. But I'm trying to set up one more account and am running into issues. I put in all the same settings as for our other accounts, but it won't allow me to create or copy any sub-folders into the InBox folder for this account (it tells me it cannot and this is "probably due to a limitation of your imap server")...but I can do so for the other accounts on the same server. Also, when I try to view the Imap Folders, it says "The...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Outlook 2003 rules question again
I have a rule so that after the e-mail comes in, it searches for specific words in the e-mail header. I set up this rule to search for sober.worm since I'm receiving a copious amount of spam e-mails which include the sober.worm attachment. I set the rule to delete the e-mails indefinately but it does not delete it. It just sends the e-mails to the junk mail folder. I would like to have it delete the e-mails forever. What can I do to achieve this? -- Nocturnal @ http://www.randomfix.com Also, I did get it to work a few times but the rule stops working. Another thing is if ...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...

What's wrong with my XSL
I get no output from this file: <?xml version="1.0" standalone="yes"?> <Specials xmlns="http://tempuri.org/Specials.xsd"> <Trip> <ID>0</ID> <Lieux>Canada</Lieux> <Hotel>Clarendon</Hotel> <Depart>1er janvier</Depart> <Duree>1 semaine</Duree> <Prix>450\$</Prix> <ToutCompris>false</ToutCompris> <Rating>4.5</Rating> <Description>Hotel dans le vieux Qu�bec</Description> </Trip> </Specials> Usin...

Outlook Question
What causes url links not work? I'm experiencing problems with it, and cannot get them to show the URL Links. Clicking the Links doesn't help. ...

VC++ .NET 2002: error C2509: 'OnPrepareDC' : member function not declared in 'CEg06aView'
Hi all I am learning the OnPrepareDC Function and ShowFont Helper Function from the Ex06a example of the book 'Programming With Microsoft Visual C++ .NET Core Reference' by G. Shepherd with D. Kruglinski. I started my new MFC Project 'Eg06a' from the MFC Wizard and added/edited the OnPrepareDC Function and ShowFont Helper Function in the Eg06aView.cpp and Eg06aView.h files - see the attached files below. When I did 'Build' on my project 'Eg06a', I got the following error: c:\Documents and Settings\Scott H. Chang\My Documents\Visual Studio Projects\Eg06a\Eg06aVi...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...

Query question 12-11-07
I have a question that I hope you all can help me with. When I run a query, the results come back with multiple lines of data for an order because of multiple critereas in another field. Here is an example of the data returned to my query: Order Status A In Process A Sent B In Process C In Process I would like to see only the data for orders that have not been shipped, and totally exclude data for orders that have been sent. In the example above, I would like my query results to show Orders B and C, but no data for A since it has al...

Translating Formulas to Functions
I'm hoping someone here can set me on the right path via an example so that I can complete this onerous task. Currently I have a spreadsheet for fantasy football that I maintain. The Workbook (Excel 2003) has the following worksheets Sheet 1 = Named Totals Sheets 2 - 18 = Named Week1, Week2, Week3, etc to Week17 Sheet19 = Status Sheet 20 = Roster Roster has 5 columns, 1 for each player position (QB, RB, WR, K, Def) Under each column is listed the person or team for that slot. Status has 18 columns and 44 rows. Some rows are blank or just contain a description. Column 1...