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
1	a1	1		a1	4	21
2	b2	2		b2	3	16
3	a1	3		c3	2	13
4	c3	4		d4	1	5
5	d4	5
6	b2	6
7	a1	7
8	b2	8
9	c3	9
10	a1	10

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E1 =COUNTIF(A:A,D1) F1 =SUMIF(A:A,D1,B:B) <tbody> </tbody>

<tbody>
</tbody>

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.