Countif help

C

Clay

I have data I want to perform a countif based on certian criteria. I want to
count the Catalog and Non-Catalog parts for each Tool Size. I have a table
that can use the formula's to choose the Tool Size and I want to count the
Catalog vs. Non- Catalog parts found for that tool size. I will show my data
table and my summary table where I want to perform the counts.

Data table:
Tool_Specific Catalog Category
EWR - 4.75 Catalog
ALD - 4.75 Catalog
CTN - 8 Non-Catalog
EWR - 4.75 Non-Catalog
EWR - 4.75 Non-Catalog
EWR - 4.75 Catalog

Summary Table:
Catalog Category EWR - 4.75 ALD - 4.75 CTN - 8
Catalog
Non-Catalog
Grand Total
 
T

Toppers

Assuming Data table Sheet1) is columns A&B and first formula for Summary
table (Sheet2) is in B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$100)=B$1),--(Sheet1!$B$2:$B$100)=$A2))

B1=Tool size e.g EWR - 4.75
A2="Catalog".

Copy a across and down (one row) your Summary table

Grand Total can be a simple SUM.

HTH
 
P

PCLIVE

With your Data Table starting at A1 (with Headings, Tool_Specific in A1 and
Catalog Category in B1), and your Summary Table starting at H1 (with
Headings, Catalog Category in H1 and the Tool sizes in I1, J1 and K1).

I2 =SUMPRODUCT(--($A$2:$A$7=$I$1),--($B$2:$B$7=H2))
I3 =SUMPRODUCT(--($A$2:$A$7=$I$1),--($B$2:$B$7=H3))
I4 =I2+I3

J2 =SUMPRODUCT(--($A$2:$A$7=$J$1),--($B$2:$B$7=H2))
J3 =SUMPRODUCT(--($A$2:$A$7=$J$1),--($B$2:$B$7=H3))
J4 =J2+J3

K2 =SUMPRODUCT(--($A$2:$A$7=$K$1),--($B$2:$B$7=H2))
K3 =SUMPRODUCT(--($A$2:$A$7=$K$1),--($B$2:$B$7=H3))
K4 =K2+K3
 
C

Clay

I tried your formula and it works, but my data is actually in different
worksheets and I cannot have it work. Here is my formula
'=SUMPRODUCT(--('SAP BOM'!$C$2:$C$1367)=C$3),--('SAP BOM'!$W$2:$W$1367)=$B4))

SAP BOM column "C" is where the Tool Size is and C3 is Tool Specific in my
Summary table & SAP BOM column "W" is where it is Catalog or Non-Catalog" and
B4 is Catalog in my summary table

Can you help. When I press enter on the formula it gives me #NA while
fixing the formula by taking away the comma and the two end brackets. HELP!

Thanks
 
C

Clay

Nevermind. It was beacuse there were two values in the column with #NA.
Therfore it was not able to count them. Thanks for the help.
 
Top