| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif help
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif help
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 "Clay" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif help
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 "Clay" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif help
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 "PCLIVE" wrote: 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 "Clay" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif help
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. "Clay" wrote: 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 "PCLIVE" wrote: 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 "Clay" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF | Excel Worksheet Functions | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Linear Mode
