COUNTIF * Quantities

[jrevard]

Hello,

I have a sheet that has a column of numbers, many of them repeated within this column. In another column I am counting the number of occurrences of each number within the first column. Working well.

I have another column in between these columns that has a quantity. That is, a number may appear more than once in the first column AND may have multiple occurrences within that single row. I need to know the total number of occurrences of a number times each quantity (sometimes quantity is 1 of course).

Cat out of the bag: These are part numbers and quantities on multiple orders. I need to count the number of times an item has appeared in a given range, and how many times for each order.

I hope I have been clear, please reply if clarification is needed. Any help is appreciated.

Thanks!!

jrevard

 

[Eric W]

Try:

	A	B	C	D	E	F
a1	a1
b2	b2
a1	c3
c3	d4
d4
b2
a1
b2
c3
a1

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E1[/TH]
[TD="align: left"]=COUNTIF(A:A,D1)[/TD]
[/TR]
[TR]
[TH]F1[/TH]
[TD="align: left"]=SUMIF(A:A,D1,B:B)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


If you're looking at a specific range, you can change the whole column references in the formulas to the range you want. Hope this helps.

 

[jrevard]

Awesome! I think you fixed it. Just so I understand your answer: "Use SUMIF() rather than COUNTIF()." Let me know if I misunderstood. Thanks!

e^4 - e = 51.88

 

[Eric W]

Correct! SUMIF should tell you the total for each part.


Incidentally, I've had that avatar for over 2 years, and you're the first person who's mentioned it! Don't forget the bars on the left and right, which essentially mean: round up to the next integer.

 

[jrevard]

Cool!! I don't know how I've never seen those bars before...

Thanks for the help, made my day and super glad I didn't need VBA.