Solution with SumProduct?

Novice here,

I need to have an output of more than just a find/sum of two columns
If the below were on columns A,B and C

name--activity--hours
bob--change--5
bob--change--4
bob--app	--4
john--app--3
john--app--5
john--change--2
bill--hardware--3
bill--hardware--1
bill--app--2
bill--app--4

What I would like to do is output how many hours of the app, chang
and/or hardware activity did john do but not just in the numerica
sense.

Looking for  John--App--8 then on the next row John--change--2 but I d
not want to display a John--Hardware--0 as I do not care about 0 hour
for a certain activity. I have used SumProduct to get the hourly tota
but I cannot find an easy way to output this with name and activit
especially when the data will be wiped and renewed weekly

--
boobo
-----------------------------------------------------------------------
boobot's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3466
View this thread: http://www.excelforum.com/showthread.php?threadid=54438

0
5/22/2006 6:10:14 PM
excel 39879 articles. 2 followers. Follow

2 Replies
788 Views

Similar Articles

[PageSpeed] 35

Why not build a table on another sheet with say Change in B1, App in C1,
etc., Bob in A2, Bill in A3, etc. then in B2 add

=SUMPRODUCT(--(Sheet1!$A$2:$A$200=$A2),--(Sheet1!$B$2:$B$200=B$1),Sheet1!$C$
2:$C$200)

copy down and across, and just suppress zeroes (Tools>Options>General,
uncheck zero values checkbox)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"boobot" <boobot.28812z_1148321704.2939@excelforum-nospam.com> wrote in
message news:boobot.28812z_1148321704.2939@excelforum-nospam.com...
>
> Novice here,
>
> I need to have an output of more than just a find/sum of two columns.
> If the below were on columns A,B and C
>
> name--activity--hours
> bob--change--5
> bob--change--4
> bob--app --4
> john--app--3
> john--app--5
> john--change--2
> bill--hardware--3
> bill--hardware--1
> bill--app--2
> bill--app--4
>
> What I would like to do is output how many hours of the app, change
> and/or hardware activity did john do but not just in the numerical
> sense.
>
> Looking for  John--App--8 then on the next row John--change--2 but I do
> not want to display a John--Hardware--0 as I do not care about 0 hours
> for a certain activity. I have used SumProduct to get the hourly total
> but I cannot find an easy way to output this with name and activity
> especially when the data will be wiped and renewed weekly!
>
>
> --
> boobot
> ------------------------------------------------------------------------
> boobot's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=34667
> View this thread: http://www.excelforum.com/showthread.php?threadid=544389
>


0
bob.phillips1 (6510)
5/22/2006 6:46:25 PM
If you have weekly updates, I would recommend to use a pivot table in this 
case.  If you use a dynamic named range as the origin, it will react to the 
changes in the data with just refreshing the table each week.

Hope this helps,
Miguel.

"boobot" wrote:

> 
> Novice here,
> 
> I need to have an output of more than just a find/sum of two columns.
> If the below were on columns A,B and C
> 
> name--activity--hours
> bob--change--5
> bob--change--4
> bob--app	--4
> john--app--3
> john--app--5
> john--change--2
> bill--hardware--3
> bill--hardware--1
> bill--app--2
> bill--app--4
> 
> What I would like to do is output how many hours of the app, change
> and/or hardware activity did john do but not just in the numerical
> sense.
> 
> Looking for  John--App--8 then on the next row John--change--2 but I do
> not want to display a John--Hardware--0 as I do not care about 0 hours
> for a certain activity. I have used SumProduct to get the hourly total
> but I cannot find an easy way to output this with name and activity
> especially when the data will be wiped and renewed weekly!
> 
> 
> -- 
> boobot
> ------------------------------------------------------------------------
> boobot's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34667
> View this thread: http://www.excelforum.com/showthread.php?threadid=544389
> 
> 
0
5/22/2006 6:56:02 PM
Reply:

Similar Artilces:

Function SUMPRODUCT (or COUNTIFS) for 2 or more options?
Hi guys! I'm using Excel 2003 and for instance I have a following table: A B 1 2008 W 2 2008 W 3 2008 M 4 2009 M I'd like to know how many 2008 year is in the column A which has letter "W" for example. The correct answer is 2 and can get this with this function: =SUMPRODUCT(--(A1:A4=2008),--(B1:B4="W")) The function COUNTIFS works only in 2007 :( So that's okay. But now I'm changing the B column to following: A B 1 2008 W1 2 2008 W2 3 2008 M1 4 2009 M2 And now I'd like to know how many 2008 year is in the colum...

Final solution to MAPI Error Message
-- JOHN HASKINS After experiencing the error generated message for all mapi problems trying to use the send email functions in Office 2007, including Word 2007, I now have solved the mystery. It was incredible all the postings I have read, dating back to 2005 about this problem. To make a long story short, follow this Microsoft link. http://support.microsoft.com/default.aspx?scid=kb;EN=US;918792 If the typed in link is not highlighted, just copy and paste to your browser. The main Topics of this article [ Article ID: 918792 - Dec 15, 2009- revision 4 ] is to 1. verify your...

Supporting two executables in a solution
I use VS2008 and I need a solution that contains 2 executables (two different processes) of which one should launch the other. 1. Does C# support such launching(with some paramters) an how? 2. Does VS2008 suport that-should I simply add the other excutables as I would do with ordinary dll? Thanks in advance Morris hi Morris, On 06.02.2010 16:06, Morris wrote: > I use VS2008 and I need a solution that contains 2 executables (two > different processes) of which one should launch the other. > 1. Does C# support such launching(with some paramters) an how? http://msdn...

Which files and folders from the two solutions must be merged to combine web sites solutions
If I understand what I'm reading on the Internet the only way to combine two website solutions and store them in one folder on a host is to make them into one website solution. And there are certain files and folders in a solution that have predetermined names and there can only be one of them. And that means combining some files from two folders into one folder (like the App_Code files) and combining some data from two files into one file (like web.config) It would be great if I knew which files there can only be one of, as well as which folders there can only be one of....

talent management solution/Compenation Management Solution
Hello, My company is looking for a Talent Management Solution/Compensation Management Solution. If anyone has any experience with any products, please post. Thank you Jack ...

SSMS Solutions
is there a way to set the solution to remain collapsed when it opens. I have some solutions that have many projects. I've tried saving everything with everything collapsed. When I re-open it, everything is expanded. Manually collapsing everything each time seems crazy. Is there a way to combat this? thanks in advance, ...bob Hi Bob Have you tried using the visual studio shortcut: Collapse all tree nodes NUM + - Collapses all nodes in the current tree view. Expand all tree nodes NUM + * Expands all nodes in the current tree view. http://msdn.micro...

How can I use a cell reference in Sumproduct array formula?
I have a table with dates down the and employee numbers across the top. I have SUMPRODUCT formulas in all the cells to gather data from named arrays from a database in the spreadsheet. I'd like to avoid munually changing (either individually or with REPLACE) date and employee number references in each formula in each cell. my formula looks like: {=SUMPRODUCT((Date=DATEVALUE("11/4/04"))*(Audempno=33758)*(Units))} also, I used a previous suggestion from this forum on another similar spreadsheet (successfully...for fiscal 3/05)with copying the whole spreadsheet and changing the d...

IE8 connection issues needs a more permanent solution
I was going to ask a question about connection problems with IE8 but it seems a lot of other people have beaten me to it. Unlike most people, I am on dialup so I thought it was having issues because of that although I did not have the same problems with IE7. Now I see that connection type has nothing to do with it. Like everyone else, I am experiencing intermitent problems. Often reloading the page solves the problem. The diagnostics produces no helpful results. And so on. I have read the helpful advice being offered but I have a problem with that. Seems it is up to us to make a...

solutions
Get the solutions manuals you need at affordable cost. Contact us at samedwards247(at)gmail(dot)com Elementary Differential Equations Bound (2nd Ed.,Werner E. Kohler, Johnson) Precalculus: Graphical, Numerical, Algebraic (7th Ed., Franklin Demana, Bert K. Waits, Gregory D. Foley & Daniel Kennedy) University Physics with Modern Physics with Mastering PhysicS, 12/E, Hugh D. Young, Roger A. Freedman Introduction to Electrodynamics (3rd Ed., David J. Griffiths) Introduction to Quantum Mechanics (2nd Ed., David J. Griffiths) Quantum Mechanics: An Accessible Introduction (Robert Scherrer) Ele...

Fun with SUMPRODUCT
Trying to solve for the following: Count if 1) Priority = "Must" AND 2) Status <> "Closed", "Accepted", or "Testing" AND 3) Functional Area begins with "RPT" AND 4) Estimated Effort = "XL" Wondering how best to modify the formula below to represent parts 2 and 3 above. How to represent contains "rpt" or begins with "rpt" (if wildcards were permitted this would be too easy!) and accommodate the variable status values we wish not to include. SUMPRODUCT(--(C2:C345=&q...

Sumproduct #24
I want to sum the total weight of a shipment. I have weight per carton in (a), and nr of cartons in c. In C1 I want a formula that would take the weight per cartop (per line) multiply it by the nr of cartons (same line) and then add it all together. I think I must use sumproduct. Thaks Weights in A1:A20; number of cartons in B1:B20 Total weight =SUMPRODUCT(A1:A20, B1:B20) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Esrei" <Esrei@discussions.microsoft.com> wrote in message news:DD066425-007F-4724-A708-7D6C78AAC930@microsoft.com... &g...

Where to adv/sell outlook solutions?
Does anyone know of a site I can usee to advertise or even sell my outlook forms and solutions? Thanks in advance... Tim Try http://www.slipstick.com -- send a message to the webmaster there.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Tim Brocklehurst" <timbrock@btinternet.com> wrote in message = news:d434ld$at2$1@sparta.btinternet.com... > Does anyone know of a site I can usee to advertise or even sell my = outlook=...

Count if or SumProduct
I have two columns : column A defines the make/model; column B numerically defines how many were sold . Note: There are multiple entries for the same makes/models due to different territories. I have a separate worksheet for which I need to summarize this info. What would be the best formula for this task? I guess I am lost on what to enter to count. Any help is greatly appreciated! Thank you, Hi, Try this. The lookup value is in A1 of the currebt sheet =SUMPRODUCT((Sheet1!A1:A20=A1)*(Sheet1!B1:B20)) Mike "Brandy" wrote: > I have two c...

Looking for EDI solution
Hi Folks, One of my clients needs to start accepting invoices from a major vendor using the EDI 810 format. Does anyone have anything good (or cautions) to say about any 3rd party EDI integrations out there? Leslie -- Leslie Vail, CPA, MCT, MVP MCBSP-Application for Microsoft Dynamics GP MCBSP-Installation and Configuration for Microsoft Dynamics GP cell: 972-814-8550 fax: 972-692-7472 We're implementing Vsync now (coming from eBridge, who did not suit us, but have many customers). Things are progressing well. -- Tim Foster "Leslie Vail [MVP]" wrote: > Hi Fol...

Is there a solution
Does anyone have a solution to this problem? More information would be helpful, I don't believe most of us who read these posting are very good at mind reading. -- Neil <francisco.casco@gmail.com> wrote in message news:1182538884.571781.202830@z28g2000prd.googlegroups.com... > Does anyone have a solution to this problem? > -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 22 Jun 2007, francisco.casco@gmail.com wrote: > Does anyone have a solution to this problem? Yes. - -- John Mayson <john@mayson.us> Austin, Texas, USA -----BEGIN PGP SIGNATURE--...

No Buffer space available, any permanent solution ?
Dear all, I have been experiencing this problem before but i can't find any permanent solution. I have tried searching for the solutions in the net but after reading all threads , restarting is the only solution provided. This problem occurs when my HQclient is attempting to connect. Thru VPN , they're connecting. Is their any other solution ? would increasing my ram will be a solution ? hi Greg, I can understand how much worried you are with this problem, the possible thing is that your connectivity to the HQ. Editting the registry is not for beginners, if you don't kn...

The solution
Hello. I'm not sure why, but when I click on the postings telling me that a solution is just a click away, nothing happens. I'm so close to balancing my checkbook, but so far. Please, MVPs, can one of you re-post the instructions so that maybe I can get the new posting open? Or, even better, e-mail it? The e-mail is dana_merle@hotmail.com. Thanks a bunch!!! Redmond, July 18, 2004, 22:00 PDT I'm sorry that this post is so late, and I know that folks are frustrated with the lack of communication. We have been on the verge of good news all afternoon (and evening), and it...

SUMPRODUCT between date range
I have successfully used SUMPRODUCT thanks to this discussion group but am having a problem trying to capture a date range. Is it possibly the way I’m entering the date? HELP! I woke up last night dreaming about this… Date of Referral Homeless/At-Risk Received MV Services? 09/02/06 Homeless yes 09/15/06 Homeless yes 10/02/06 At-Risk yes 09/20/06 Homeless no I need to capture the number of students fo...

New Website Comparing POS Integration Solutions
Hey guys, We recently launched a new Website (http:// poswebstoreintegrations.com) that compares various POS Webstore integration and eCommerce solutions. We hope that the site can provide new insight into these solutions. In our first post, we compared Nitrosell with Kosmos Central. Please provide any feedback on our comparison. If you want to provide some additional comparisons between the two, or with a new solution, please feel free to do so. Thanks! POS Webstore Integrations Staff On Jul 8, 4:29=A0pm, POS Webstore Integrations <poswebstoreintegrati...@gmail.com> wrote: > He...

many reports solution
Hi, I have a web page to let user to define customized report. For now, just one and two dimension reports. And db has 11 entity tables, and a few tables to join them togeter. 11 entity joined in different path, in different way (1:m. m:m), directly and in-directly. Now the problem is the user want each dimension base on any entity. Then I ned generate P(11,1) + P(11.2) = 11*1 + 11*10 = 121 combinations of group by . Multiple what to report (count(sth) , Sum(this), Sum(that)), I need generate 363 reports. The web page, in users eys, is so simple, just 3 dropdowns an...

Solution: RequiredInfoState
Error message after 'successful' install: A required registry value is missing: RequiredInfoState Freshly built Windows 2000 PC with SP4 - no virus checkers of any kind. Error occurred when I installed to D:\Program Files\Microsoft Money D:\Program Files\MSMoney it WORKED fine when I installed to D:\Utils\MSMoney C:\Program Files\Microsoft Money possible it has an issue with being installed to D:\Program Files In microsoft.public.money, Steve wrote: >Error message after 'successful' install: A required registry value is >missing: RequiredInfoState > >Freshly b...

Microsoft Visio Partner Webcast
The practice of coordinating physical workspaces with the resources and work of an organization is a major component of Facilities Management. This can be a daunting task without the proper tool set. The August 15th Microsoft Visio webcast features two partners with Visio based solutions that help to improve your ability to communicate your Facilities Management designs, proposals, and ideas more effectively. Join us on Tuesday, August 15th at 9am PST for an informative Visio webcast on Facilities Management Solutions and experience one partner's solution that brings the third dimension t...

sumproduct in pivot
2003 version excel In using a pivot table I would like to be able to customize using sumproduct instead of sum or average, etc. Is there a way to do this? =?Utf-8?B?b2ZmaWNlZ2lybA==?= <officegirl@discussions.microsoft.com> wrote in news:808A66C1-A15D-41DB-B8E7-EAF03E00153F@microsoft.com: > 2003 version excel > > In using a pivot table I would like to be able to customize using > sumproduct instead of sum or average, etc. Is there a way to do > this? > You have reached a newsgroup dedicated to Microsoft Access, the relational database. Please hang up and try...

EDI Solutions #2
Wanted to know which people had EDI and what solutions there were, not to mention your experience with them. We are looking for a small EDI solution to automate mostly just the 810 and 850 transactions in and out. Hi, If you are interested we can offer a customized EDI solutions as per your requirements. Please drop a emai to discuss. Regards, Manick Manager-Mid Market Solutions manick.m@hotmail.com "Dnelson" wrote: > Wanted to know which people had EDI and what solutions there were, not to > mention your experience with them. We are looking for a small EDI solution ...

Do you have solution for me!!??
MAPI or unspacified service provider. ID no: c1050000 Exchange System Manager Could you provide more information about when the error occurs. -- Mark Fugatt Microsoft Limited This posting is provided "AS IS" with no warranties, and confers no rights. "ToMMaS MooRe" <ToMMaS MooRe@discussions.microsoft.com> wrote in message news:05D5F21C-040D-4B3F-8743-3F19151ED64D@microsoft.com... > MAPI or unspacified service provider. ID no: c1050000 Exchange System > Manager check this out http://support.microsoft.com/kb/301003/EN-US/ Chhers, oz -- Best regards, ...