Funding SUM of cells that are in the same row as another loo

Perry M · Mar 26th, 2002, 07:32 PM

I an importing date from an AS400 system into Excel. I then need to find all of the values in column B that are the same, and add the values in column D that are in the same rows.
I am having a difficult time finding a way to do this. Of course it doesn't help that I am fairly inexperienced with formulas, and VB functions.

Dave Hawley · Mar 26th, 2002, 07:36 PM

Hi Perry

If you Sort the data all the doubles will be together. You could then probably use the "Subtotal" feature found under Data.

Or perhaps a simple pivot table?

Perry M · Mar 26th, 2002, 07:45 PM

Thanks for the response, but it seems I left out an important piece of the puzzle. The method of accumulating this data needs to reside on a blank form. When I import the data from the AS400, I have no idea how many of each match I will retrieve. I only know which columns the data will appear in. I am trying to "automate" the compiling of the data I import daily. With many entries each day, theis is a very time consuming task to do manually.

Dave Hawley · Mar 26th, 2002, 07:48 PM

Perry, I don't see why that matters????

Perry M · Mar 26th, 2002, 08:08 PM

Well Dave, this may just be a case of my ignorance, but I didn't think I could use the subtotals function without knowing exactly which cells I wanted the subtotals from???

Perry M · Mar 26th, 2002, 08:22 PM

OK Dave, I see what that does now. I don't have my file here at home, but I tried the function you suggested, and it may just do it. I was not familiar with the subtotal function and misunderstood what it did.
Thanks for the tip. I will give it a try at work tomorrow.

RichardS · Mar 26th, 2002, 08:23 PM

Perry,

What Dave is saying is sort the data on column B so they are grouped together, then use ths Data|Subtotal menu option to sub-total column D at each change in column B

Richard

I see you got the idea just as I was clarifying. All's well that ends well. Hope it's what you want.

Richard

[ This Message was edited by: RichardS on 2002-03-26 19:25 ]

Perry M · Mar 27th, 2002, 05:40 AM

The subtotal function works great for grouping my totals, but the next problem is that I need to move that total to another page to chart it too. Is there a way to do this without knowing which cells my totals are in, or do I need to attack this from a different angle?
Always a new twist the problem huh?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 07:32 PM	#1
Perry M New Member Join Date: Mar 2002 Posts: 14	I an importing date from an AS400 system into Excel. I then need to find all of the values in column B that are the same, and add the values in column D that are in the same rows. I am having a difficult time finding a way to do this. Of course it doesn't help that I am fairly inexperienced with formulas, and VB functions.

Mar 26th, 2002, 07:36 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Perry If you Sort the data all the doubles will be together. You could then probably use the "Subtotal" feature found under Data. Or perhaps a simple pivot table?

Mar 26th, 2002, 07:45 PM	#3
Perry M New Member Join Date: Mar 2002 Posts: 14	Thanks for the response, but it seems I left out an important piece of the puzzle. The method of accumulating this data needs to reside on a blank form. When I import the data from the AS400, I have no idea how many of each match I will retrieve. I only know which columns the data will appear in. I am trying to "automate" the compiling of the data I import daily. With many entries each day, theis is a very time consuming task to do manually.

Mar 26th, 2002, 07:48 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Perry, I don't see why that matters????

Mar 26th, 2002, 08:08 PM	#5
Perry M New Member Join Date: Mar 2002 Posts: 14	Well Dave, this may just be a case of my ignorance, but I didn't think I could use the subtotals function without knowing exactly which cells I wanted the subtotals from???

Mar 26th, 2002, 08:22 PM	#6
Perry M New Member Join Date: Mar 2002 Posts: 14	OK Dave, I see what that does now. I don't have my file here at home, but I tried the function you suggested, and it may just do it. I was not familiar with the subtotal function and misunderstood what it did. Thanks for the tip. I will give it a try at work tomorrow.

Mar 26th, 2002, 08:23 PM	#7
RichardS Board Regular Join Date: Feb 2002 Location: Victoria, Australia Posts: 761	Perry, What Dave is saying is sort the data on column B so they are grouped together, then use ths Data\|Subtotal menu option to sub-total column D at each change in column B Richard I see you got the idea just as I was clarifying. All's well that ends well. Hope it's what you want. Richard [ This Message was edited by: RichardS on 2002-03-26 19:25 ]

Mar 27th, 2002, 05:40 AM	#8
Perry M New Member Join Date: Mar 2002 Posts: 14	The subtotal function works great for grouping my totals, but the next problem is that I need to move that total to another page to chart it too. Is there a way to do this without knowing which cells my totals are in, or do I need to attack this from a different angle? Always a new twist the problem huh?