sum rows in Excel 1

[JCAA]

I get a file with work orders from a client each week. I built an
Access db system to be able to manipulate the data into our forecast
system but recently my client bought a new system and instead of
summing up the quantity needed per part they now have part number by
itself and under part number there is the same partnumber with a
workorder under the partnumber (following row). The work orders are
all a concatenation of the partnumber and a uniquely identifying
workorder number with a dash in between eg if part nummer is 43 and
workorder is 5342 the new workorder number would be 43-5342. The part
number by itself does sometime sum up the workorders into one number,
sometimes not and others falsely so it is important for me to be able
to sum up all the workorders except the top partnumber that does not
include a workorder extension. I need to be able to sum up all the
partnumbers that have a workorder extension to them. So for example if
I have a list
43
43-5342
43-5325
43-1221
I want to be able to sum up the 43-5342, 43-5325, 43-1221

All help is greatly appreciated as I am not very good at writing Excel
Macros!

Thanks!

JCA

 

[ETID]

if your list is in col A,...then in an empty cell type
=countif(A:A,"=43-5342&quot
etc...

 

[TropicalFred]

If each part number is in a separate column and there is only one part number per column, go to the bottom of the column and use the formula "=COUNTA(A1:A20)-1". Leave out the quotation marks, of course, and replace "A1" with the first cell of the list and "A20" with the last cell in the list.

If you have several part number columns with different numbers of work orders, go to the column with the longest list, put in the formula and copy it across the same row. Blank cells will be ignored by the "=COUNTA()" function.

Good Luck,
Fred

 

[xlbo]

This may give you an idea - array entered with CTRL+SHIFT+ENTER

=SUM((LEFT(A1:A100,2)=&quot;43&quot*(A1:A100<>43)*(B1:B100))

will sum all values in B where the left 2 digits are 43 except for where the only value in the cell is 43

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[KenWright]

Just a variation on Geoff's really, but doesn't need to be array entered:-

=SUMPRODUCT((LEFT(A2:A5,3)=&quot;43-&quot*(B2:B5))

Assumes that a hyphen is present in all sub tier workorders

Regards
Ken.............

 

[ETID]

Or use the pivot table,...very flexible and powerful

 

[JCAA]

Thank you all for your replies. These seems to be great tips but I think I need them to be modified further as I have a couple of hundred different main part numbers and several hundred workorders under. I would like to sum them together without having to type in the data week after week.
Does that make sense, is it even possible? Look for the main part#'s and then sum up all the sub part#'s that have the same main part?
Thanks a million,

JCA

 

[xlbo]

So - are you looking to create a summary sheet ??? or do this all in one sheet ?? What is the desired outcome ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[ETID]

make a col. in your xls called main_part_num...

then in that col...

=left(a2,(search(&quot;-&quot;,a2,1)-1)

fill down


then build a pivot using the wizard, placing main_part_num in the page area, your existing part num in the row
and what ever you want to count,sum averge etc... in the data area.

try it it's free and can easily be deleted if not desirable.

 

[SkipVought]

Hi,

Make yourself a separate column with this formula that identifies the values with a &quot;-&quot;

=IF(ISERROR(FIND("-",A:A)),0,1)

Using an Advanced Filter select records haveing 1 in this calculated field - filter to a new location.

Use the new location as source data for a pivot table that does a Count of partnumber for each partnumber.

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JCAA]

Thanks so much you all, I am really grateful! I ended up using Skips last example and that works like a charm. Again THANKS!

 

[ETID]

Here Skip...found this Star just laying on the ground here for Ya, JCAA must have dropped it.