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

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

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

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

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

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