How to count a value in different sheets?

Hi there,

I want to count how many times a value/text is appearing in Sheet 2?

For example:

Product "XYZ123" in department "A".
I want to know how many times this product is appearing in a specific department in Sheet 2
I have over 10,000 products... so is there a way to set one formula on a grid and drag it down?

Thank you.

Hi Richard,

Thank you for replying.

Attach is the Excel sheet.

In Sheet 1. product name, department and qty.
In sheet 2. product name, department, qty and serial no.

I want to know...
Whether product from sheet one from a specific department is available in Sheet 2 with the same qty wihtin the same department? If there is any discrepeny how much is that? does the total qty of each department match with the qty of sheet 2 ..

Hope this makes sense.

Thank you.

This will give quantities on Sheet2

in D2 (of Sheet1)


=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,A2,Sheet2!$B:$B,B2)

in E2

=C2-D2

Awesome!!
Thanks .. You're a Guru!