| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I get a subtotal to appear in a column within a spreadshee
I am trying to total the number of line items per ID# in a particular column
within a spreadsheet, as pictured below. I do not want to create subtotal rows, nor do I want to work with levels. I've tried various things with the IF, SUMIF and COUNTIF functions, to no avail. I am fairly new to all this and would greatly appreciate any and all suggestions. Thanks! Number of Line Items per ID# Count ID# 1 1000 1 1000 3 1 1000 1 1 1001 1 1 1002 1 1 1003 1 1004 1 1004 1 1004 1 1004 1 1004 6 1 1004 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I get a subtotal to appear in a column within a spreadshee
In Cell A2 enter:
=IF(C2=C3,"",SUMPRODUCT(--(C$2:C2=C2))) and copy down... HTH "Elizabeth" wrote: I am trying to total the number of line items per ID# in a particular column within a spreadsheet, as pictured below. I do not want to create subtotal rows, nor do I want to work with levels. I've tried various things with the IF, SUMIF and COUNTIF functions, to no avail. I am fairly new to all this and would greatly appreciate any and all suggestions. Thanks! Number of Line Items per ID# Count ID# 1 1000 1 1000 3 1 1000 1 1 1001 1 1 1002 1 1 1003 1 1004 1 1004 1 1004 1 1004 1 1004 6 1 1004 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I get a subtotal to appear in a column within a spread
It worked! Thank you so much for your help!
"Jim May" wrote: In Cell A2 enter: =IF(C2=C3,"",SUMPRODUCT(--(C$2:C2=C2))) and copy down... HTH "Elizabeth" wrote: I am trying to total the number of line items per ID# in a particular column within a spreadsheet, as pictured below. I do not want to create subtotal rows, nor do I want to work with levels. I've tried various things with the IF, SUMIF and COUNTIF functions, to no avail. I am fairly new to all this and would greatly appreciate any and all suggestions. Thanks! Number of Line Items per ID# Count ID# 1 1000 1 1000 3 1 1000 1 1 1001 1 1 1002 1 1 1003 1 1004 1 1004 1 1004 1 1004 1 1004 6 1 1004 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I get a subtotal to appear in a column within a spread
Hi Elizabeth
Jim's solution works fine, in your example, as there is only a value of 1 in column B against each ID# I took the existence of this column to indicate that there could be values other than 1 (otherwise the column is superfluous). If there are values other than 1, then you will need to add the range in B to the Sumproduct formula. -- Regards Roger Govier "Elizabeth" wrote in message ... It worked! Thank you so much for your help! "Jim May" wrote: In Cell A2 enter: =IF(C2=C3,"",SUMPRODUCT(--(C$2:C2=C2))) and copy down... HTH "Elizabeth" wrote: I am trying to total the number of line items per ID# in a particular column within a spreadsheet, as pictured below. I do not want to create subtotal rows, nor do I want to work with levels. I've tried various things with the IF, SUMIF and COUNTIF functions, to no avail. I am fairly new to all this and would greatly appreciate any and all suggestions. Thanks! Number of Line Items per ID# Count ID# 1 1000 1 1000 3 1 1000 1 1 1001 1 1 1002 1 1 1003 1 1004 1 1004 1 1004 1 1004 1 1004 6 1 1004 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I get a subtotal to appear in a column within a spreadshee
the formula is for Row 3 cwhere the column you want added is G. I performs a
subtotal only when the data in a row doesn't equal the data in the next row. =IF(G3<G4,COUNTIF(G:G,G3),"") "Elizabeth" wrote: I am trying to total the number of line items per ID# in a particular column within a spreadsheet, as pictured below. I do not want to create subtotal rows, nor do I want to work with levels. I've tried various things with the IF, SUMIF and COUNTIF functions, to no avail. I am fairly new to all this and would greatly appreciate any and all suggestions. Thanks! Number of Line Items per ID# Count ID# 1 1000 1 1000 3 1 1000 1 1 1001 1 1 1002 1 1 1003 1 1004 1 1004 1 1004 1 1004 1 1004 6 1 1004 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I get a subtotal to appear in a column within a spreadshee
Hi Elizabeth
One way, in A2 enter =IF(C2=C3,"",SUMPRODUCT(($C$2:$C$100=C2)*$B$2:$B$1 00)) and copy down. Change ranges to suit the amount of your data, but note that Sumproduct cannot take whole column ranges (apart from XL2007) -- Regards Roger Govier "Elizabeth" wrote in message ... I am trying to total the number of line items per ID# in a particular column within a spreadsheet, as pictured below. I do not want to create subtotal rows, nor do I want to work with levels. I've tried various things with the IF, SUMIF and COUNTIF functions, to no avail. I am fairly new to all this and would greatly appreciate any and all suggestions. Thanks! Number of Line Items per ID# Count ID# 1 1000 1 1000 3 1 1000 1 1 1001 1 1 1002 1 1 1003 1 1004 1 1004 1 1004 1 1004 1 1004 6 1 1004 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ignore zero in column for subtotal | Excel Discussion (Misc queries) | |||
| Can I set up a default footer that will appear in every spreadshee | Excel Worksheet Functions | |||
| How do I make the 1st column constant in viewing a wide spreadshee | Excel Worksheet Functions | |||
| can I put total value from subtotal into next column | Excel Discussion (Misc queries) | |||
| List box or combo box on an Excel spreadshee... | Excel Worksheet Functions | |||
Hybrid Mode
