Counting Function
 

I have a document with a list of part numbers and quantities. I would like
to create a function that allows me to update a new sheet where all the part
numbers are listed. I would like it to show how many of a certain part
number show up on the sheet calculating the quantity with in the line. With
the example below, I would like to answer the question: How many Part 1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

=COUNTIF($A$2:$A$500,D2)

replace the cells with the dollar signs with the actual range of your sheet
and cell range for the part numbers, then D2 is the first cell with a part
number, copy down as long as needed


--
Regards,

Peo Sjoblom



"Amber" wrote in message
...
I have a document with a list of part numbers and quantities. I would like
to create a function that allows me to update a new sheet where all the
part
numbers are listed. I would like it to show how many of a certain part
number show up on the sheet calculating the quantity with in the line.
With
the example below, I would like to answer the question: How many Part 1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

Try this:

=SUMIF(Sheet1!A1:A4;"Part 1"; Sheet1!B1:B4)


--

Rodrigo Ferreira


"Amber" escreveu na mensagem
...
I have a document with a list of part numbers and quantities. I would like
to create a function that allows me to update a new sheet where all the
part
numbers are listed. I would like it to show how many of a certain part
number show up on the sheet calculating the quantity with in the line.
With
the example below, I would like to answer the question: How many Part 1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

Doh! I went on your subject instead of looking at your example but it should
of course be sumif instead

=SUMIF(Part_Range,D2,Sum_Range)

--
Regards,

Peo Sjoblom



"Peo Sjoblom" wrote in message
...
=COUNTIF($A$2:$A$500,D2)

replace the cells with the dollar signs with the actual range of your
sheet and cell range for the part numbers, then D2 is the first cell with
a part number, copy down as long as needed


--
Regards,

Peo Sjoblom



"Amber" wrote in message
...
I have a document with a list of part numbers and quantities. I would
like
to create a function that allows me to update a new sheet where all the
part
numbers are listed. I would like it to show how many of a certain part
number show up on the sheet calculating the quantity with in the line.
With
the example below, I would like to answer the question: How many Part
1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

Thank you for your help. I have one other thing that I need to calculate.

I need to know how many unique numbers that I have in a set of data but only
considering certain numbers. I have 2 different types of special numbers and
need to consider both types.

Example: Part 1
Part 2
Part 1
1Special Number 1Special Number
2Special Number 2Special Number
1Special Number
2Special Number

Any help that you can give would be great. Please let me know if this does
not make sense.



"Rodrigo Ferreira" wrote:

Try this:

=SUMIF(Sheet1!A1:A4;"Part 1"; Sheet1!B1:B4)


--

Rodrigo Ferreira


"Amber" escreveu na mensagem
...
I have a document with a list of part numbers and quantities. I would like
to create a function that allows me to update a new sheet where all the
part
numbers are listed. I would like it to show how many of a certain part
number show up on the sheet calculating the quantity with in the line.
With
the example below, I would like to answer the question: How many Part 1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

Your example is confusing, but what you state is that you wish to only count
uniques meeting a certain criteria.

See if you can fit this to your data configuration:

Say the items to count are in A1 to A10.
Each item is of a certain type, and the type is in B1 to B10.

To count the unique items in Column A that match the particular type
*entered* in C1, that are in Column B, try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B10=C1),MATCH(A1:A10,A1:A10,0)),R OW(1:10)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Amber" wrote in message
...
Thank you for your help. I have one other thing that I need to calculate.

I need to know how many unique numbers that I have in a set of data but only
considering certain numbers. I have 2 different types of special numbers and
need to consider both types.

Example: Part 1
Part 2
Part 1
1Special Number 1Special Number
2Special Number 2Special Number
1Special Number
2Special Number

Any help that you can give would be great. Please let me know if this does
not make sense.



"Rodrigo Ferreira" wrote:

Try this:

=SUMIF(Sheet1!A1:A4;"Part 1"; Sheet1!B1:B4)


--

Rodrigo Ferreira


"Amber" escreveu na mensagem
...
I have a document with a list of part numbers and quantities. I would
like
to create a function that allows me to update a new sheet where all the
part
numbers are listed. I would like it to show how many of a certain part
number show up on the sheet calculating the quantity with in the line.
With
the example below, I would like to answer the question: How many Part
1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

I am sure that my first post is confusing.... :) I am new at this.

I don't believe this is going to work but I may be missing something. Here
is a second attempt at my explanation.

We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these part
numbers begin with either "2D7" or "CCA". I have done a similar calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D is the
column that has the invoice numbers. The problem I am having is that the
column with the part numbers has all part number but I only want the special
numbers to be calculated.

I hope this helps.


"RagDyeR" wrote:

Your example is confusing, but what you state is that you wish to only count
uniques meeting a certain criteria.

See if you can fit this to your data configuration:

Say the items to count are in A1 to A10.
Each item is of a certain type, and the type is in B1 to B10.

To count the unique items in Column A that match the particular type
*entered* in C1, that are in Column B, try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B10=C1),MATCH(A1:A10,A1:A10,0)),R OW(1:10)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Amber" wrote in message
...
Thank you for your help. I have one other thing that I need to calculate.

I need to know how many unique numbers that I have in a set of data but only
considering certain numbers. I have 2 different types of special numbers and
need to consider both types.

Example: Part 1
Part 2
Part 1
1Special Number 1Special Number
2Special Number 2Special Number
1Special Number
2Special Number

Any help that you can give would be great. Please let me know if this does
not make sense.



"Rodrigo Ferreira" wrote:

Try this:

=SUMIF(Sheet1!A1:A4;"Part 1"; Sheet1!B1:B4)


--

Rodrigo Ferreira


"Amber" escreveu na mensagem
...
I have a document with a list of part numbers and quantities. I would
like
to create a function that allows me to update a new sheet where all the
part
numbers are listed. I would like it to show how many of a certain part
number show up on the sheet calculating the quantity with in the line.
With
the example below, I would like to answer the question: How many Part
1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

So, are you saying that you have part #'s that are special, *and* there are
duplicates:

2D7123
2D7456
2D7789
2D7123
2D7456
3D7123
3D7456

And you want the unique count for the above to be 3?
OR
Do you want the count for the above to be 5?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Amber" wrote in message
...
I am sure that my first post is confusing.... :) I am new at this.

I don't believe this is going to work but I may be missing something. Here
is a second attempt at my explanation.

We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these part
numbers begin with either "2D7" or "CCA". I have done a similar
calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D is
the
column that has the invoice numbers. The problem I am having is that the
column with the part numbers has all part number but I only want the
special
numbers to be calculated.

I hope this helps.


"RagDyeR" wrote:

Your example is confusing, but what you state is that you wish to only
count
uniques meeting a certain criteria.

See if you can fit this to your data configuration:

Say the items to count are in A1 to A10.
Each item is of a certain type, and the type is in B1 to B10.

To count the unique items in Column A that match the particular type
*entered* in C1, that are in Column B, try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B10=C1),MATCH(A1:A10,A1:A10,0)),R OW(1:10)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Amber" wrote in message
...
Thank you for your help. I have one other thing that I need to
calculate.

I need to know how many unique numbers that I have in a set of data but
only
considering certain numbers. I have 2 different types of special numbers
and
need to consider both types.

Example: Part 1
Part 2
Part 1
1Special Number 1Special Number
2Special Number 2Special Number
1Special Number
2Special Number

Any help that you can give would be great. Please let me know if this
does
not make sense.



"Rodrigo Ferreira" wrote:

Try this:

=SUMIF(Sheet1!A1:A4;"Part 1"; Sheet1!B1:B4)


--

Rodrigo Ferreira


"Amber" escreveu na mensagem
...
I have a document with a list of part numbers and quantities. I would
like
to create a function that allows me to update a new sheet where all
the
part
numbers are listed. I would like it to show how many of a certain
part
number show up on the sheet calculating the quantity with in the
line.
With
the example below, I would like to answer the question: How many
Part
1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

She already started a new thread


--
Regards,

Peo Sjoblom



"Ragdyer" wrote in message
...
So, are you saying that you have part #'s that are special, *and* there
are duplicates:

2D7123
2D7456
2D7789
2D7123
2D7456
3D7123
3D7456

And you want the unique count for the above to be 3?
OR
Do you want the count for the above to be 5?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Amber" wrote in message
...
I am sure that my first post is confusing.... :) I am new at this.

I don't believe this is going to work but I may be missing something.
Here
is a second attempt at my explanation.

We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these
part
numbers begin with either "2D7" or "CCA". I have done a similar
calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D is
the
column that has the invoice numbers. The problem I am having is that the
column with the part numbers has all part number but I only want the
special
numbers to be calculated.

I hope this helps.


"RagDyeR" wrote:

Your example is confusing, but what you state is that you wish to only
count
uniques meeting a certain criteria.

See if you can fit this to your data configuration:

Say the items to count are in A1 to A10.
Each item is of a certain type, and the type is in B1 to B10.

To count the unique items in Column A that match the particular type
*entered* in C1, that are in Column B, try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B10=C1),MATCH(A1:A10,A1:A10,0)),R OW(1:10)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Amber" wrote in message
...
Thank you for your help. I have one other thing that I need to
calculate.

I need to know how many unique numbers that I have in a set of data but
only
considering certain numbers. I have 2 different types of special numbers
and
need to consider both types.

Example: Part 1
Part 2
Part 1
1Special Number 1Special Number
2Special Number 2Special Number
1Special Number
2Special Number

Any help that you can give would be great. Please let me know if this
does
not make sense.



"Rodrigo Ferreira" wrote:

Try this:

=SUMIF(Sheet1!A1:A4;"Part 1"; Sheet1!B1:B4)


--

Rodrigo Ferreira


"Amber" escreveu na mensagem
...
I have a document with a list of part numbers and quantities. I
would
like
to create a function that allows me to update a new sheet where all
the
part
numbers are listed. I would like it to show how many of a certain
part
number show up on the sheet calculating the quantity with in the
line.
With
the example below, I would like to answer the question: How many
Part
1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4

Counting Function
 

Thanks Peo ... saw it a couple of minutes after I posted the above.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
She already started a new thread


--
Regards,

Peo Sjoblom



"Ragdyer" wrote in message
...
So, are you saying that you have part #'s that are special, *and* there
are duplicates:

2D7123
2D7456
2D7789
2D7123
2D7456
3D7123
3D7456

And you want the unique count for the above to be 3?
OR
Do you want the count for the above to be 5?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Amber" wrote in message
...
I am sure that my first post is confusing.... :) I am new at this.

I don't believe this is going to work but I may be missing something.
Here
is a second attempt at my explanation.

We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these
part
numbers begin with either "2D7" or "CCA". I have done a similar
calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D
is the
column that has the invoice numbers. The problem I am having is that
the
column with the part numbers has all part number but I only want the
special
numbers to be calculated.

I hope this helps.


"RagDyeR" wrote:

Your example is confusing, but what you state is that you wish to only
count
uniques meeting a certain criteria.

See if you can fit this to your data configuration:

Say the items to count are in A1 to A10.
Each item is of a certain type, and the type is in B1 to B10.

To count the unique items in Column A that match the particular type
*entered* in C1, that are in Column B, try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B10=C1),MATCH(A1:A10,A1:A10,0)),R OW(1:10)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead
of the
regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Amber" wrote in message
...
Thank you for your help. I have one other thing that I need to
calculate.

I need to know how many unique numbers that I have in a set of data but
only
considering certain numbers. I have 2 different types of special
numbers and
need to consider both types.

Example: Part 1
Part 2
Part 1
1Special Number 1Special Number
2Special Number 2Special Number
1Special Number
2Special Number

Any help that you can give would be great. Please let me know if this
does
not make sense.



"Rodrigo Ferreira" wrote:

Try this:

=SUMIF(Sheet1!A1:A4;"Part 1"; Sheet1!B1:B4)


--

Rodrigo Ferreira


"Amber" escreveu na mensagem
...
I have a document with a list of part numbers and quantities. I
would
like
to create a function that allows me to update a new sheet where all
the
part
numbers are listed. I would like it to show how many of a certain
part
number show up on the sheet calculating the quantity with in the
line.
With
the example below, I would like to answer the question: How many
Part
1's
have been sold? This should be 6.

Part 1 2
Part 2 3
Part 3 1
Part 1 4