Counting Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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
 
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)
 
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.
 
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)),ROW(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!
=====================================================


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.
 
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.
 
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?
 
Back
Top