#### HELP! Totals by two variables?

```How do I total up the amount of an item by month and also name?

For example:
Colum A:      Colum B:            Colum C:
Dates:           Units Sold          Seller:
1/5/05                 8                   Josh
1/6/05                 10                 Josh
1/15/05               7                   Rick
2/8/05                 1                   Josh
2/20/05              13                  Rick
3/5/05                 25                 Rick

So I am trying to set up a chart where January Gives the totals for Josh and
Rick in seperate cells and etc. for each month.

```
 0
Xandlyn (3)
3/10/2005 7:35:01 AM
excel.misc 78881 articles. 5 followers.

6 Replies
489 Views

Similar Articles

[PageSpeed] 13

```Try Pivot Table ..

Some quick steps to ease you in?

Assuming the sample data given is in Sheet1,

Click anywhere within the data

Click Data > Pivot Table Report > Next > Next

In step 3 of the wizard:
drag Seller and drop within ROW area
drag Dates and drop within COLUMN area
drag Units Sold and drop within DATA area
(it'll appear as Sum of Units Sold)

Click Finish

The Pivot Table (PT) will appear
in a new sheet to the left of Sheet1

In the PT,

Right-click on Dates > Group and Outline > Group

In the Group dialog,
you'll see that By "Months" has been pre-selected

Click OK

Voila, you'll get what you want .. :

Sum of Units Sold__Dates:
Seller:___________Jan Feb Mar Grand Total
Josh_____________18_1________19
Rick______________7_13__25____45
Grand Total_______25__14__25____64

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Xandlyn" <Xandlyn@discussions.microsoft.com> wrote in message
news:8A63D40C-77B7-4A79-8102-915086293336@microsoft.com...
> How do I total up the amount of an item by month and also name?
>
> For example:
> Colum A:      Colum B:            Colum C:
> Dates:           Units Sold          Seller:
> 1/5/05                 8                   Josh
> 1/6/05                 10                 Josh
> 1/15/05               7                   Rick
> 2/8/05                 1                   Josh
> 2/20/05              13                  Rick
> 3/5/05                 25                 Rick
>
> So I am trying to set up a chart where January Gives the totals for Josh
and
> Rick in seperate cells and etc. for each month.
>
>

```
 0
demechanik (4694)
3/10/2005 8:10:46 AM
```Personally, I don't rate Pivot Tables. If you want a non-Pivot table
solution, try this

ON another sheet
In B1-M1, put the dates 1/1/05, 2/1/05, etc
In A2:An, put the Seller's names

In B2:
=SUMPRODUCT(--(Month(Sheet1!A1:A1000)=Month(B\$1)),--(Year(Sheet1!A1:A1000)=Y
ear(B\$1)),--(Sheet1!C1:C1000=\$A2),Sheet1!B1:B1000)

and copy across and down

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Xandlyn" <Xandlyn@discussions.microsoft.com> wrote in message
news:8A63D40C-77B7-4A79-8102-915086293336@microsoft.com...
> How do I total up the amount of an item by month and also name?
>
> For example:
> Colum A:      Colum B:            Colum C:
> Dates:           Units Sold          Seller:
> 1/5/05                 8                   Josh
> 1/6/05                 10                 Josh
> 1/15/05               7                   Rick
> 2/8/05                 1                   Josh
> 2/20/05              13                  Rick
> 3/5/05                 25                 Rick
>
> So I am trying to set up a chart where January Gives the totals for Josh
and
> Rick in seperate cells and etc. for each month.
>
>

```
 0
bob.phillips1 (6510)
3/10/2005 9:40:44 AM
```"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote
> Personally, I don't rate Pivot Tables. ..
Left room for a non-pivot table solution to breeze by <bg>
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----

```
 0
demechanik (4694)
3/10/2005 10:18:02 AM
```
"Max" <demechanik@yahoo.com> wrote in message
news:ez1\$MqVJFHA.2640@TK2MSFTNGP09.phx.gbl...
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote
> > Personally, I don't rate Pivot Tables. ..
> Left room for a non-pivot table solution to breeze by <bg>

And I blew <ebg>

```
 0
bob.phillips1 (6510)
3/10/2005 10:34:24 AM
```> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote
> > Personally, I don't rate Pivot Tables. ..

Now that's something we have in common!

Biff

>-----Original Message-----
>
>
>"Max" <demechanik@yahoo.com> wrote in message
>news:ez1\$MqVJFHA.2640@TK2MSFTNGP09.phx.gbl...
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote
>> > Personally, I don't rate Pivot Tables. ..
>> Left room for a non-pivot table solution to breeze by
<bg>
>
>And I blew <ebg>
>
>
>.
>
```
 0
biffinpitt (3172)
3/10/2005 7:16:10 PM
```"Biff" <biffinpitt@comcast.net> wrote in message
news:57f201c525a5\$9e13daa0\$a501280a@phx.gbl...
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote
> > > Personally, I don't rate Pivot Tables. ..
>
> Now that's something we have in common!

We have a third like mind, but I won't 'out' him <g>

```
 0
bob.phillips1 (6510)
3/10/2005 8:41:45 PM

Similar Artilces:

Help with String.Format
Hello, I have a winforms app with some values in the app.config file I use to create a connection string to excel. Some of the values require quotes around the values within the connection string. Here is an example of the connection string: string connectionString =3D @"Provider=3DMicrosoft.Jet.OLEDB.4.0; Data Source=3DBook1.xls;Extended Properties=3D""Excel 8.0;HDR=3DYES;"""= ; Note the quotes around the Extended Properties values. I have the following String.Format code (which doesn=92t work): sConnStr =3D String.Format( ...

Help me please with MS Pocket PCS and Outlook
Into Outlook 2003 I sync between my work computer and my home computer using and HP iPaq w/Pocket PC 2003. I have the same Outlook version on both computers. I have the MS Pocket PCS addin software on my work computer and thought I had it on my home computer (I did). Today I changed around some contact folders at work, my PPC sync(ed) and I went home. I got home and tried to sync with my home computer and to my supprise all (more than 300) of my work contacts are gone! I looked for the PCS app but could not find it. So I downloaded, When I attempted to install it it wanted to uninstall i...

Match() - Offset()
Often I see mention of Offset(), Match(), & Index() XL functions. Could someone big-picture these functions for me? I have not used these functions yet in data lookup or evaluation. Shame on me. That said, The concept of "Index" I understand from db work. "Match" seems vaguely logical to me for "lookup" work. "Offset" means nothing to me yet. Reading the XL help or reference books do not give me typical uses and why's of the functions. My focus is the accounting/financial world. Using XP Pro & XL XP Pro SP1. Dennis This file b...

Macro Help 05-20-10
how do i assign a value of a cell to a variable in a macro This sets the variable: Sub dural() Dim r As Range, v As Variant Set r = Range("A1") v = r.Value End Sub to set the cell, reverse the equation. -- Gary''s Student - gsnu201003 "BigO" wrote: > how do i assign a value of a cell to a variable in a macro ...

Hi! I have been working with Outlook for years - it was always a great feature - but now it is critical since I have an Ipaq that I need to sync to it. Outlook somehow is no longer working/on my computer. I have tried repeatedly to reinstall - to "fix" - anything with NO luck. I get an icon to click on for Outlook and then it goes directly to personal folders (nothing actually happens). Every other component of Office 2000 is working perfectly. Any help would be GREATLY appreciated! I am desperado!!!! Have you run "Detect and Repair" from under help in Outl...

I am faced with a strange problem. I was working with worksheet in Excel when there was a power shutdown. The worksheet was in saved condition. When power resumed my worksheet simply vanished. Now whenever I try to access the worksheet I get the message that worksheet could not be found. As my pc is window xp pro. the worksheet should have saved itself as it does in word program. I have cheeked Recycle Bin and ran search program without any success. System Restore is of no help as it was off. Is there any way to retrieve the file. TIA. ...

Advanced Filter by Macro does not work anymore under Excel 2007
Hi to all, I've actually some problems with excel 2007 This macro : ---------------------------------------------------------------------------------------------------- Sub FiltreAvance() Sheets("BDD").Range("A3:V25000").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("A1:D3"), CopyToRange:=Range("A11:S11"), Unique:= False End Sub ---------------------------------------------------------------------------------------------------- launch by a button from the sheet "Filter" to display the result of the filter on t...

Reported error (0X800CC0F) the connection to the server was interrupted
I am receiving this error message on a client workstation, none of the other 4 users on the LAN are receiving this error. They are using Outlook 2002 connected through an ADSL modem. The user still can send and receive e-mails but this error message always appears. Any one have any ideas? Reported error (0X800CC0F) the connection to the server was interrupted Sean For those of you that have been getting "Sending and Receiving reported error .... connection to server was interrupted" as I have, try creating a new HTTP email account, send and receive a couple of messages from that...

Help with my Excel Database
The following is what I need help with: (1) I have a main sheet where I am entering my data. I have defined a dynamic range for this data so that if I enter a new row or column in the sheet then this data will be included in the dynamic range. I want to know what I need to do to copy the data from the main sheet into asecond sheet so that when I enter a new row/column or amend the existing data in the first sheet then the second sheet will automatically update. I presume it is something to do with referencing the dynamic range from the first sheet into the second sheet. ...

WhenI send and receive mail a message pops up on the sreen saying: Contacting then server for information. It then runs the green bars kinda like it is completing something and goes away. My email seems to be working ok but this just started. It is slowing me down very much. Has anyone information on this? All comments are appreciated very much. Keith Is it only on reading messages or elsewhere also? "Keith" <keithgirdley@blomand.net> wrote in message news:JOWdnej4T-nnY7jUnZ2dnUVZ_tHinZ2d@neonova.net... > WhenI send and receive mail a message pops up on the sreen s...

I am trying to create a survey form with multiple choice radio buttons. I want to add descriptive text that appears when you float your cursor over the button (like that for a toolbar icon). I've scoured Excel help and couldn't find anything. I don't have experience with macros or Visual Basic but am willing to try if someone can hold my hand through it. If the radio button is in a cell, add a comment to the cell. Then the instructions will appear when the mouse hovers over the cell. -- Damon Longworth Don't miss out on the 2005 Excel User Conference Sept 16th and 17th...

help needed for closing modeless dialog
hello, I have a dialog based application. Inthe OnInitDialog() of this dialog i am poping up a modeless dialog.Now i am placing a menu in the parent dialog.what i want is that when i close the modeless dialog by clicking the red cross button on right hand corner then only i should be able to pop up modeless dialog from the menu.Do i need to handle the WM_CLOSE;if yes how? It could also be sufficient is closing makes dialog invisible and then menu option makes it visible...... thanks in advance When the X is clicked, a WM_SYSCOMMAND message is sent with the ID SC_CLOSE indicated....

synchiing two computers?
Is there a way to synch Outlook on two computers? I end up with some email on my laptop and some on my desktop - it would be nice if the two could be synched so that I could have all of my email on both computers. Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Bill Andersen asked: | Is there a way to synch Outlook on two computers? | I end up with some...

Two different queries return same results
I’m calculating average returns for stocks over two time periods; 30-days and 90-days. These two queries always return the same results for different time periods and I have no idea why. PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice30_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)>=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; PARAMETERS [Forms]![frmMstr]![c...

Help with XslCompiledTransform
I am using XslCompiledTransform to transform XML into Excel 2003 SpreadsheetML. In order to get the resulting XML file to open in Excel, we need the following two lines at the top of the file: <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> I can get the <?mso-application ...> line by using an <xsl:processing-instruction> element, but for the life of me I can't get XslCompiledTransform to include the basic <?xml...> line. When creating the XmlWriterSettings for use in the transform, I have not altered the OmitXmlDeclarati...

Help mfcdll not working in vb
I am trying to write one MFC dll. I have created one test function declared like this in the cpp of dll _declspec(dllexport)void test() { AFX_MANAGE_STATE(AfxGetStaticModuleState()); AfxMessageBox("Hello"); } the problem is that this dll works fine when i use it in vc project but in VB it gives the following error Can't find dll entry point test in test.dll However when i declare this function in test.def it starts working in VB but gives an error in VC. I am not able to find out the reason for this behaviour. I will be thankful for any help on this. Sumit Marwal ...

Help! IF function is too limited
Hi! I've created a drop-down list of cities in a cell, under which there are two more cells to be filled out with the address and zip codes corresponding to each of the cities. How can I do this, knowing that the original list is a three-column list made of city-address-zip? -- Ringo ------------------------------------------------------------------------ Ringo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27502 View this thread: http://www.excelforum.com/showthread.php?threadid=470178 Use VLOOKUP into the original; list using the DV value =VLOOKUP(B...

need help simplying my formula..
for cell D25: =IF(A25=0,0,IF(RIGHT(B25,4)="Road","Road",IF(RIGHT(B25,6)="Bridge","Road",IF(RIGHT(B25,7)="Highway","Road")))) this means that, B25 could be "Bay bridge", "County Road", "Blue Highway", etc etc and the result for D25 would be "Road" is there a simpler way of writing this formula? 2nd question: for cell B5 IF(A1=0,0,IF(C2=4,IF(A1=5,5,IF(A1=6,6),A1))) can i write this any simpler? tia One way for the first part: =IF(A25=0,0,IF(OR(RIGHT(B25,4)="Road",RIGHT(B25,6)=&qu...

SMTP Message Queue full. Help!
Exchange2k3 SBS. No email was going in or out so I took a look at the queues in system manager. I found a whole bunch of emails that were stuck and appeared to be coming from the postmaster account. Is my server a victim of being a spam relay? When I run relay tests it says that my server is not a relay. How do I get rid of all these bogus queues? I know I can delete the messages but its the queues I want to remove. the queues cannot be deleted. those queues are created on demand based on the email destination. What you need to do is to find out what is causing those mails and solve th...

Help in turning off underlining and blue font
Hello, Does anyone know if there is a way to turn off the automatic underlining and blue font of the text when entering a email address in a cell? Thanks, Bob Tools / Autocorrect options / Autoformat Tab / Replace Network paths with hyperlinks - Deselect it -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ----------------------------------------------------------------------...

Who is This STEVE... ? Does he really help.. Others
I wonder Who is This STEVE... Many question for learning purpose I go His name Pops up.... and I always see.. "Help .. with \$\$\$\$\$\$".... We Leaner.... Well Already spent Money for Books... "Ahmed" <khalakmu@hotmail.com> schreef in bericht news:27B4D66C-3FA3-45E2-A98C-087E7C12A1BC@microsoft.com... > I wonder Who is This STEVE... Many question for learning purpose I go His name > > Pops up.... and I always see.. "Help .. with \$\$\$\$\$\$".... > > We Leaner.... Well Already spen...

Help with SUMIF function
Hi, I'm trying to add up a range of cells based on two IF statements, but with no luck :( What I was trying to do was: =Sum(If (G3:G2224,"Home")*(M3:M2224,"Blue"),P3:P2224) This doesn't call up the result that i want,which is the sum of the P cells if range G = home and range M = blue. Can anyone suggest a work around or point out to me what I am doing wrong? All help would be very much appreciated! -- Regards, Dan Hi Try =SUMPRODUCT((G3:G2224,"Home")*(M3:M2224,"Blue")*P3:P2224) -- Regards Roger Govier "Confused Dan"...