Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JP Long
 
Posts: n/a
Default Subtotaling list of different items

The data below shows a section of the information we are trying to subtotal.
What I am looking for is a formula (or combination of formulas) that will
provide a subtotal for each specific item listed. An example is illustrated
on the first two SKUs.


30478FJYPK20MIN EA 275
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3 30478FJYPK20MIN = 384
30518FRTRP SET 2
30518FRTRPHC SET 4 30518FRTRP = 6
3068FM100 EA 358
3068FM100 EA 8
3068SPEM4P EA 112
3068SPEM4P20MIN EA 275
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21

I think what I want to do is search the sheet for each specific item and
then have the formula calculate a total for all the like SKUs...? I'm
totally lost on what will work...
--
J. Paul Long
Training Manager
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default Subtotaling list of different items

I modified something I was using which was similar (except I was just doing
counts.) If you don't mind the information being in 2 columns and the SKU
being listed first instead of last like so:

30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3
30518FRTRP SET 2 30518FRTRP 2
30518FRTRPHC SET 4 30518FRTRPHC 4
3068FM100 EA 358 3068FM100 366
3068FM100 EA 8
3068SPEM4P EA 112 3068SPEM4P 112
3068SPEM4P20MIN EA 275 3068SPEM4P20MIN 363
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21 3068SPEN4P20MIN 21

Also notice I did not put in the = sign. I'm sure I could have using the
concatenation symbol (&), but just in case you wanted to use the numbers as
numbers, I figured if you really wanted, you could use a column with a very
small width and put the "=" sign there.
The two formulas a
=IF(COUNTIF(OFFSET(D$2:D3,-1,0),A3)0,"",A3)
=IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"")
Note: It appears that you need to put those formulas in rows 3 or below or
you will get a bunch of #REF errors.

"JP Long" wrote:

The data below shows a section of the information we are trying to subtotal.
What I am looking for is a formula (or combination of formulas) that will
provide a subtotal for each specific item listed. An example is illustrated
on the first two SKUs.


30478FJYPK20MIN EA 275
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3 30478FJYPK20MIN = 384
30518FRTRP SET 2
30518FRTRPHC SET 4 30518FRTRP = 6
3068FM100 EA 358
3068FM100 EA 8
3068SPEM4P EA 112
3068SPEM4P20MIN EA 275
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21

I think what I want to do is search the sheet for each specific item and
then have the formula calculate a total for all the like SKUs...? I'm
totally lost on what will work...
--
J. Paul Long
Training Manager

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JR
 
Posts: n/a
Default Subtotaling list of different items

I assume you want to sum the numbers for a given sku, and not count the
sku's. If so, you will need a list of the individual sku numbers to use as
criteria for a SUMIF formula. =SUMIF($A$1:$A$75,E1,$C$1:$C$75) What this
says is you are looking for the criteria (E1) in the column A range, when you
find it, sum the values in the column C range. The ranges need to be
"locked" with dollars signs so you can copy the formula down to subsequent
criteria without them re-adjusting themselves. Another way to do this would
be to run a Pivot Table on the data.

"JP Long" wrote:

The data below shows a section of the information we are trying to subtotal.
What I am looking for is a formula (or combination of formulas) that will
provide a subtotal for each specific item listed. An example is illustrated
on the first two SKUs.


30478FJYPK20MIN EA 275
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3 30478FJYPK20MIN = 384
30518FRTRP SET 2
30518FRTRPHC SET 4 30518FRTRP = 6
3068FM100 EA 358
3068FM100 EA 8
3068SPEM4P EA 112
3068SPEM4P20MIN EA 275
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21

I think what I want to do is search the sheet for each specific item and
then have the formula calculate a total for all the like SKUs...? I'm
totally lost on what will work...
--
J. Paul Long
Training Manager

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JP Long
 
Posts: n/a
Default Subtotaling list of different items

Thank you for the help. It is greatly appreciated. I have a couple of
questions... pasting the first formula gets me the SKU information;
however,t he second formula does not generate any information. What exactly
is the second formula calculating (which data)?

When pasting this formula, will I need to paste it in a cell for each
different SKU or is there a way to automate this entry?
--
J. Paul Long
Training Manager


"Kleev" wrote:

I modified something I was using which was similar (except I was just doing
counts.) If you don't mind the information being in 2 columns and the SKU
being listed first instead of last like so:

30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3
30518FRTRP SET 2 30518FRTRP 2
30518FRTRPHC SET 4 30518FRTRPHC 4
3068FM100 EA 358 3068FM100 366
3068FM100 EA 8
3068SPEM4P EA 112 3068SPEM4P 112
3068SPEM4P20MIN EA 275 3068SPEM4P20MIN 363
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21 3068SPEN4P20MIN 21

Also notice I did not put in the = sign. I'm sure I could have using the
concatenation symbol (&), but just in case you wanted to use the numbers as
numbers, I figured if you really wanted, you could use a column with a very
small width and put the "=" sign there.
The two formulas a
=IF(COUNTIF(OFFSET(D$2:D3,-1,0),A3)0,"",A3)
=IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"")
Note: It appears that you need to put those formulas in rows 3 or below or
you will get a bunch of #REF errors.

"JP Long" wrote:

The data below shows a section of the information we are trying to subtotal.
What I am looking for is a formula (or combination of formulas) that will
provide a subtotal for each specific item listed. An example is illustrated
on the first two SKUs.


30478FJYPK20MIN EA 275
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3 30478FJYPK20MIN = 384
30518FRTRP SET 2
30518FRTRPHC SET 4 30518FRTRP = 6
3068FM100 EA 358
3068FM100 EA 8
3068SPEM4P EA 112
3068SPEM4P20MIN EA 275
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21

I think what I want to do is search the sheet for each specific item and
then have the formula calculate a total for all the like SKUs...? I'm
totally lost on what will work...
--
J. Paul Long
Training Manager

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default Subtotaling list of different items

The second formula goes to the right of the first formula (in the next
column.) It is calculating the total (384 for the first row of data.) Then,
just copy the formulas down. The second formula refers to the cells that the
first formula is in (to see if there is data in the cell next to it) so that
it only shows data when there is something in the previous column.
Let's see. Here is the data with the cells showing the results of the
formulas:
30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
And here is the data with the cells showing the actual formulas (of course,
it is wrapping so take that into account. Hope this helps clear it up.

30478FJYPK20MIN EA 275 =IF(COUNTIF(OFFSET(D$2:D3,-1,0),A3)0,"",A3) =IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"")



"JP Long" wrote:

Thank you for the help. It is greatly appreciated. I have a couple of
questions... pasting the first formula gets me the SKU information;
however,t he second formula does not generate any information. What exactly
is the second formula calculating (which data)?

When pasting this formula, will I need to paste it in a cell for each
different SKU or is there a way to automate this entry?
--
J. Paul Long
Training Manager


"Kleev" wrote:

I modified something I was using which was similar (except I was just doing
counts.) If you don't mind the information being in 2 columns and the SKU
being listed first instead of last like so:

30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3
30518FRTRP SET 2 30518FRTRP 2
30518FRTRPHC SET 4 30518FRTRPHC 4
3068FM100 EA 358 3068FM100 366
3068FM100 EA 8
3068SPEM4P EA 112 3068SPEM4P 112
3068SPEM4P20MIN EA 275 3068SPEM4P20MIN 363
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21 3068SPEN4P20MIN 21

Also notice I did not put in the = sign. I'm sure I could have using the
concatenation symbol (&), but just in case you wanted to use the numbers as
numbers, I figured if you really wanted, you could use a column with a very
small width and put the "=" sign there.
The two formulas a
=IF(COUNTIF(OFFSET(D$2:D3,-1,0),A3)0,"",A3)
=IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"")
Note: It appears that you need to put those formulas in rows 3 or below or
you will get a bunch of #REF errors.

"JP Long" wrote:

The data below shows a section of the information we are trying to subtotal.
What I am looking for is a formula (or combination of formulas) that will
provide a subtotal for each specific item listed. An example is illustrated
on the first two SKUs.


30478FJYPK20MIN EA 275
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3 30478FJYPK20MIN = 384
30518FRTRP SET 2
30518FRTRPHC SET 4 30518FRTRP = 6
3068FM100 EA 358
3068FM100 EA 8
3068SPEM4P EA 112
3068SPEM4P20MIN EA 275
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21

I think what I want to do is search the sheet for each specific item and
then have the formula calculate a total for all the like SKUs...? I'm
totally lost on what will work...
--
J. Paul Long
Training Manager



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default Subtotaling list of different items

After looking over your questions again, I guess I should have mentioned that
the formulas do not repeat data so you can copy them to consecutive rows
without worrying about SKUs or totals repeating. Also, just to clarify, the
data and formulas I used started in row 3. I'll paste a couple of rows and
include Column and row labels to see if that helps. Also, I assume you know
that the ranges I used were representative of the test data and would need to
be expanded for your data.

A B C D E
3 30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
4 30478FJYPK20MIN EA 21
5 30478FJYPK20MIN EA 85
6 30478FJYPK20MIN EA 3
7 30518FRTRP SET 2 30518FRTRP 2
8 30518FRTRPHC SET 4 30518FRTRPHC 4
9 3068FM100 EA 358 3068FM100 366
10 3068FM100 EA 8
11 3068SPEM4P EA 112 3068SPEM4P 112
12 3068SPEM4P20MIN EA 275 3068SPEM4P20MIN 363
13 3068SPEM4P20MIN EA 85
14 3068SPEM4P20MIN EA 3
15 3068SPEN4P20MIN EA 21 3068SPEN4P20MIN 21

And now just 2 rows with the formulas showing:
A B C D E
3
30478FJYPK20MIN EA 275 =IF(COUNTIF(OFFSET(D$2:D3,-1,0),A3)0,"",A3) =IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"")
4
30478FJYPK20MIN EA 21 =IF(COUNTIF(OFFSET(D$2:D4,-1,0),A4)0,"",A4) =IF(COUNTIF(A$3:A$15,D4)0,SUMIF(A$3:A$15,D4,$C$3: $C$15),"")


HTH

"Kleev" wrote:

The second formula goes to the right of the first formula (in the next
column.) It is calculating the total (384 for the first row of data.) Then,
just copy the formulas down. The second formula refers to the cells that the
first formula is in (to see if there is data in the cell next to it) so that
it only shows data when there is something in the previous column.
Let's see. Here is the data with the cells showing the results of the
formulas:
30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
And here is the data with the cells showing the actual formulas (of course,
it is wrapping so take that into account. Hope this helps clear it up.

30478FJYPK20MIN EA 275 =IF(COUNTIF(OFFSET(D$2:D3,-1,0),A3)0,"",A3) =IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"")



"JP Long" wrote:

Thank you for the help. It is greatly appreciated. I have a couple of
questions... pasting the first formula gets me the SKU information;
however,t he second formula does not generate any information. What exactly
is the second formula calculating (which data)?

When pasting this formula, will I need to paste it in a cell for each
different SKU or is there a way to automate this entry?
--
J. Paul Long
Training Manager


"Kleev" wrote:

I modified something I was using which was similar (except I was just doing
counts.) If you don't mind the information being in 2 columns and the SKU
being listed first instead of last like so:

30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3
30518FRTRP SET 2 30518FRTRP 2
30518FRTRPHC SET 4 30518FRTRPHC 4
3068FM100 EA 358 3068FM100 366
3068FM100 EA 8
3068SPEM4P EA 112 3068SPEM4P 112
3068SPEM4P20MIN EA 275 3068SPEM4P20MIN 363
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21 3068SPEN4P20MIN 21

Also notice I did not put in the = sign. I'm sure I could have using the
concatenation symbol (&), but just in case you wanted to use the numbers as
numbers, I figured if you really wanted, you could use a column with a very
small width and put the "=" sign there.
The two formulas a
=IF(COUNTIF(OFFSET(D$2:D3,-1,0),A3)0,"",A3)
=IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"")
Note: It appears that you need to put those formulas in rows 3 or below or
you will get a bunch of #REF errors.

"JP Long" wrote:

The data below shows a section of the information we are trying to subtotal.
What I am looking for is a formula (or combination of formulas) that will
provide a subtotal for each specific item listed. An example is illustrated
on the first two SKUs.


30478FJYPK20MIN EA 275
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3 30478FJYPK20MIN = 384
30518FRTRP SET 2
30518FRTRPHC SET 4 30518FRTRP = 6
3068FM100 EA 358
3068FM100 EA 8
3068SPEM4P EA 112
3068SPEM4P20MIN EA 275
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21

I think what I want to do is search the sheet for each specific item and
then have the formula calculate a total for all the like SKUs...? I'm
totally lost on what will work...
--
J. Paul Long
Training Manager

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Numbering items from a list in order Natalie Excel Discussion (Misc queries) 8 October 30th 05 11:43 PM
Compare 2 columns, and create a list of items that are in both lists ruby2sdy Excel Worksheet Functions 3 October 8th 05 11:04 AM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
Making list of items to truck monthly usage Yarek Excel Discussion (Misc queries) 0 June 23rd 05 10:37 AM
Indexing items from a pick list Pank Mehta Excel Discussion (Misc queries) 1 February 3rd 05 10:29 AM


All times are GMT +1. The time now is 02:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"