Counting Question #3

I have three columns:  dates, values, and names

10/17  $300 Jim
10/17  $300 Jim
10/17  $200 Tom
10/17  $100 Jim

When I enter Jim and $300 in to two separate cells, in a third cell I
want to count the number of "days" it applies to (all the way down the
spreadsheet.) 

So in other words, there are two instances in the same day of Jim and
$300, but since it all happened on one day, the answer would be one.

Hope I explained it well.
0
jimx22 (226)
8/20/2010 10:42:44 PM
excel 39879 articles. 2 followers. Follow

2 Replies
602 Views

Similar Articles

[PageSpeed] 39

=sumproduct(--(b1:b10=x1),--(c1:c10=x2))
where x1 holds the amount, x2 holds the name and B1:B10 holds the list of 
amounts and c1:c10 holds the list of names.

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

========
If you're using xl2007+, there's a new =sumifS() formula you can read about in 
excel's help.

On 08/20/2010 17:42, JimS wrote:
> I have three columns:  dates, values, and names
>
> 10/17  $300 Jim
> 10/17  $300 Jim
> 10/17  $200 Tom
> 10/17  $100 Jim
>
> When I enter Jim and $300 in to two separate cells, in a third cell I
> want to count the number of "days" it applies to (all the way down the
> spreadsheet.)
>
> So in other words, there are two instances in the same day of Jim and
> $300, but since it all happened on one day, the answer would be one.
>
> Hope I explained it well.

-- 
Dave Peterson
0
petersod1 (224)
8/20/2010 11:25:54 PM
Thanks, Dave.  I didn't explain it well.  I'll rethink things, and if
I can't figure it out I'll try again later.

Appreciate your help.

On Fri, 20 Aug 2010 18:25:54 -0500, Dave Peterson
<petersod@XSPAMverizon.net> wrote:

>=sumproduct(--(b1:b10=x1),--(c1:c10=x2))
>where x1 holds the amount, x2 holds the name and B1:B10 holds the list of 
>amounts and c1:c10 holds the list of names.
>
>Adjust the ranges to match--but you can't use whole columns (except in xl2007+).
>
>=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
>to 1's and 0's.
>
>Bob Phillips explains =sumproduct() in much more detail here:
>http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
>And J.E. McGimpsey has some notes at:
>http://mcgimpsey.com/excel/formulae/doubleneg.html
>
>========
>If you're using xl2007+, there's a new =sumifS() formula you can read about in 
>excel's help.
>
>On 08/20/2010 17:42, JimS wrote:
>> I have three columns:  dates, values, and names
>>
>> 10/17  $300 Jim
>> 10/17  $300 Jim
>> 10/17  $200 Tom
>> 10/17  $100 Jim
>>
>> When I enter Jim and $300 in to two separate cells, in a third cell I
>> want to count the number of "days" it applies to (all the way down the
>> spreadsheet.)
>>
>> So in other words, there are two instances in the same day of Jim and
>> $300, but since it all happened on one day, the answer would be one.
>>
>> Hope I explained it well.

0
jimx22 (226)
8/21/2010 3:13:45 AM
Reply:

Similar Artilces:

Payroll Question
I am trying to set up my old office's payroll on a spreadsheet so that the new secretary doesn't have to spend so much time on payroll each week. The problem I am having is when calculating the SSI & Med, Excel automatically rounds the answer to two (2) decimals, but when I total the column it adds using the the extended figure (ex.: $323.00 * .062 = $20.026, in my column it will round the answer to $20.03, but when I total the column to show an employee the withholdings for the year, excel totals the column using the $20.026 amount. I need to have the total calculat...

macros vba question
is there a way that when I save as a file. If a cell in a worksheet says Week 1 it will copy and paste with vaules a col if if says week 2 is will copy and paste with values a different col in the worksheet You can do this with a macro. I am assuming you mean a particular cell on a particular sheet, not just any cell on any sheet. The following code checks cell A1 on Sheet1 whenever you save the workbook. If A1 contains Week 1 then column D is copied & pasted in place as values. If A1 contains Week 2 then column E is copied & pasted in place as values. Private Sub W...

Quote Template Question
I have modified the Quote.dot template and added the products line into the table. However when I generate the quote it splits the products into 1 table each. I just can't figure out how to have the products join into 1 table. I have screen shots to show how I have modified it but no way to attach it to this message for someone to review. The coding goes something like this: The Product, Qty, Unit, Unit Price, Discount, Ext Amount are in a 1 row table '' }{IF {MERGEFIELD "LineItem_Tax" } <> "" {SET ExtAmt {= MERGFIELD"LineItem_Extended_Amoun...

Question about an SQL statement
I have an SQL statement that is way to long. bd="days" bn="nights" ds="day shift" and so on. stwhere1 = "SELECT [shift],[lname],[fname],[locat1]" & _ " FROM [tbl_Roster]" & _ " WHERE [shift] = '" & bd & "' or [shift]='" & bn & "' or [shift] ='" & ds & "' or [shift]='" & dn & "'or [shift]='" & aft & "'" The above WHERE is on one line and it works. I was wonder...

Windows Live Mail question.
Using OE when messages were downloaded they were then deleted from ISP's server. This isn't happening with Live Mail, they are still on the server. Have been through the Options in Live Mail but can't find how to do this. Replies appreciated. Kenny Cargill Found this: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.windows.live.mail.desktop&tid=6a98f0d1-95d9-4868-9ed8-6aa2f7cef794&cat=en_US_0405EAE1-3A5E-559F-59E6-B48513D5B57E&lang=en&cr=US&sloc=en-us&m=1&p=1 but I have the same problem as this guy, I...

CRM 3.0 Service Calendar Unexpected Error
Well, it appears that i am not as lucky as the others that were able to get their calendar to work somehow(reference to earlier posts). We just migrated to crm 3.0, and cannot get the calendar to work(at all), i get the red X "unexpected error" and then im told to contact me(the system administrator). I go into the Event manager of the server and see something about date and time settings? There was a post recently where someone had this exact same problem, and they got it fixed, but didn't post their resolution, so i decided to see if anyone does actually have an answer ...

The messaging interface has returned an unknown error #3
Please help me. I am trying to delete email form my outlook inbox. When I click delete, I get following message: "the messaging interface has returned an unknown error. if the problem persists, restart Outlook." Does anyone know how to fix this? Thanks RJ ...

CRM 3.0
We have our main CRM and Exchange server on one domain and our test/demo CRM server on a different domain. Should we be able to forward email to the test/demo CRM accounts from Exchange when it is not in the same domain? OUr network administrator has tried to do this without success. ...

Help: I need a macro to add words every 3 lines
Hello, I understand nothing to macro but I am sure that a macro can resolve my problem. I have the following columns, for example (it is for a dictionary database): House Maison [empty line] Car Voiture [empty line] Dog Chien [empty line] etc..... The first word is always in English and the second is always french. There is only 1 column but many lines. I would like to add "\en" in front of each English word, and "\fr" in front of each French word (in the same cell - I do not want to add a colum)... as a result I should get : \en House \fr Maison \en Car \fr Voitu...

Undeliverable Message #3
I am trying to send an E-mail to Myself from an external web site. As I sent this invitation e-mail to my address on my exchange server I receive the following undeliverable message in my exchange mailbox. Rob Peters on 10/14/2004 11:51 AM The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address. < primecontract.com #5.1.1 SMTP; 550 <rob@pma-a2.com>: Sender address rejected: Blocked> This message does not appear if I send the invitatio...

Counting the number of "Y" in a row
Hi all I know this has to be pretty simple but I can't quite figure it out. I want a field at the end of each row which will count the number of fields with a "Y" and show the total number. I tried an IF statement but this went all wrong and I have tried the COUNT () statement but was not sure how to get this to count letters. Can somebody please help? Have a look in the help index for COUNTA -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mikey C" <m.cringle@gmail.com> wrote in message news:1173871396.451963.103300@p15g2000hsd.googlegroups.com....

crm 1.2 to crm 3 upgrade time out
CRM upgrade from 1.2 to 3 is failing with a timeout issue on a very large db (almost 16Gb). Any ideas? Event Logs: 15:06:31| Info| Adding attribute :StatusCode To Entity : Email 16:06:55| Error| Install exception.System.Exception: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlCli...

Word count and language settings #2
Is there any way you can change the language setting for a whole document e.g. many text boxes at the same time. Also, is there any way to count words in all text boxes in Publisher at the same time. I know you can edit to Word, but this is box by box, so takes for ever. ...

Search for my questions in 2010
When I do I search for my discussion questions (by searching for my username), I don't get any results for 2010 (even though I have submitted several questions in 2010 that have been answered). Is there a setting that I have to change in order to be able to retrieve my 2010 discussions? Click on your name in the post and then recent posts. I can see 8 posts in 2010 by you. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "KrispyData" wrote: > When I do I search for my discussion questions (by searching for my &...

Web Severices: CreatePayablesInvoice question
I am trying to use the GP web services to create a payables invoice using predetermined distrubition accounts. When I create the AP Invoice with the GetPolicyByOperation("CreatePayablesInvoice", context) and I use the debug in my VS 2005, I see that the Distributions property has an array of zero elements. How can I add my behavior information to that array? This is the code (C#) I have so far and I think it is correct but I cannot find anywhere in the GP documentation how to actuall put my desired behavior into that array of behaviors. apPolicy = wsDynamicsGP.GetPolic...

Exchange 2003 System Manager #3
I am using Exchange 2003. I have users I would like to have manage the Recipients container to manage contacts and distribution lists but nothing else. Is there a way to do this? I know I can install the System Mgr on the desktop, but it appears users must be Domain Admin members, and that's not cool. So how can I limit them to just the Recipients container? This article may help you out Daniel to get you going in the right direction: 316792 Minimum Permissions Necessary to Perform Exchange-Related Tasks http://support.microsoft.com/?id=316792 -- Randy Campbell Microsoft PSS Pleas...

Upgrade from 3.0 to 4.0 & changing physicall servers
Hello! I have question regarding physicall server change when upgrading CRM from 3.0 to 4.0? Does change of physicall server change situation with upgrade if I want all data to be also in new CRM? Thank you in advance for help. Is your new physical server going to be 32 bits or 64 bits OS? Review the following link for ideas: http://microsoft-crm.spaces.live.com/blog/cns!A2586C4AB938C065!310.entry Frank Lee, Microsoft Dynamics CRM MVP http://www.workopia.com/Links.htm "Liega Buracevska" wrote: > Hello! > I have question regarding physicall server change when upgradi...

Opening attachments #3
I am unable to open attachments in Outlook Express 6. I think this has occured because of updates. I receive the response "Invalid syntax error". The page cannot be displayed....Please try the following: Open the res://C:\WINDOWS\SYSTEM\SHDOCLC.DLL/syntax.htm#mhtml:file:/ / home page, and then look for links to the info. u want. This doesn't help. In Outlook Express 6.00 I allow attachments to be saved or opened that could potentially be a virus. ...

VLOOKUP problem #3
I'm using VLOOKUP in Sheet1 to pull in column C from Sheet2 containing percentage variances from columns A and B. The problem is that for yesterday's date, which is =TODAY()-1, it's not pulling the correct percentage. Very weird. It's pulling in 0% variance, which is not what I see on Sheet2, Column C. The odd thing is that the variances for all other days before yesterday are being pulled in correctly. Any suggestions? -- Thanks, -Kim Show us the formula you are using, please -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from e...

Questions about MX Record for E2K3
All This should be simple enough question to answer, I was wondering if an MX record has to have a mail in front of it. By that I mean mail.mycompany.com for the MX record or can I have something like e2k3.mycompany.com? Secondly, if myMX record is e2k3.mycompany.com but the FQDN is e2k3.us.mycompany.com will I get an error? I have been getting a lot of these message when I tried to send emails to comcast "e2k3.us.mycompany.com #5.5.0 smtp;521-EHLO/HELO from sender 111.111.111.111 does not map to e2k3.us.mycompany.com in DNS". And I was wondering if I can r...

Address Book Problem #3
Hi, does anybody know why the All address lists in the address book are missing after the upgrade to Outlook 2003 from Office 2000? How do I fix the problem --- to add All address lists to the address book in Outlook 2003? Thanks. Mzhang@pattan.k12.pa.us http://support.microsoft.com/default.aspx?scid=kb;en-us;287563&Product=ol2003 -- Russ Valentine [MVP-Outlook] "Matt Z." <Mzhang@pattan.k12.pa.us> wrote in message news:795201c49514$1e8195b0$a501280a@phx.gbl... > Hi, does anybody know why the All address lists in the > address book are missing after the upgrade ...

Count Function #4
Does anyone know of a function that will count how may unique items exist in a list? For example, suppose the following list: A B A C D D I would want the formula to return an answer of 4, since this is the number of unique items (A, B, C & D) and so not to count the multiples of some items. Any ideas? Many thanks in advance. Hi Matt! See: Chip Pearson: http://www.cpearson.com/excel/duplicat.htm#CountingUnique Various formulas for counting unique entries -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classificatio...

Form protect on 2 of 3 pages only
I have a 3+ page form. Page 1 and 2 are form protected with a password. The 3rd or more pages is to insert pictures documenting the information in pages 1 & 2. How can I form protect pages 1 and 2 only allowing pictures to be inserted on page 3+? Thanks Place a section break (next page) between pages 2 and 3. If there's a manual page break already in that location, delete it. Then, when you start enforcing protection on the form, you should have an option to select the sections that will be protected. Just click the applicable sections and you should be fine. I...

Noob form question
Hello Gurus, I have inherited a database (A2003): Table1 is related to Table2, based on a UniqueName field in both tables. Now, the form: If you select a name in the combo box, based on Table1 UniqueName, then it fills the other boxes with other information from Table1 relevant to the UniqueName you selected - it works perfectly. I would like to add an extra text box to the form which fills with info from a field in Table2, again based on UniqueName. Is this possible? If so, how do I specify that the data should come from a different table? All my effort so far have resulted in the...

Datagram sockets and compatibility
Here's a quote from the description of the CHATTER example "CArchive does not support datagram sockets, so all data translation would have to be handled by the program itself, or the user could only chat with those who are using the same version of CHATTER. By using the stream sockets, users of Windows 95, Windows NT ANSI, and Windows NT Unicode versions can chat with each other without concerns of version compatibility. Is there a difference in the way a UPD packet is sent/received depending on which version of Windows is being used? What are the concerns of version compaibility ...