Forum Discussion
smikes181
Jan 31, 2023Copper Contributor
Countifs Help
Hello all. Thank you in advance. This may be a duplicate post, I just cannot find the original. I am trying to use the COUNTIFS to get data from basically two or three columns. The Date column does not really come into play at this time. What I would like to end up with in a separate cell is a Total Count of all similar parts of a specific material and shape, regardless of the date delivered. I am fairly new to Excel and I can do the simple formula. In this example I was using the COUNTIFS function, starting with =COUNTIFS(L15:L64,"Steel")+SUM(I15:I64), hoping the the second part would only count the total of all Steel parts, which in the below example should equal “206”. But it still gives the sum of all parts, 419, in the Qty column. I have not ventured to the second criteria and range yet, wanting to get the total count resolved first. I appreciate any help or advice. The only other way I could think of would be to add 4 or 8 more columns to represent the criteria individually.
Delivery Date | Parts | Material | Qty |
1-Jan | Square | Steel | 10 |
1-Jan | Round | Alum | 14 |
1-Jan | Rectangle | Stainless | 19 |
1-Jan | Oval | Brass | 21 |
25-Jan | Round | Steel | 101 |
25-Jan | Oval | Brass | 54 |
25-Jan | Square | Steel | 8 |
25-Jan | Rectangle | Alum | 42 |
25-Jan | Oval | Stainless | 63 |
25-Jan | Square | Steel | 87 |
Total Steel | 206 |
| |
Total Alum | 56 |
| |
Total Brass | 75 |
| |
Total Stainless | 82 |
|
- Mark, Thank You very much!!! I will have to read up on those 3. It is confusing in that you are not need to call out the part type or the material. Again, Thank you
2 Replies
Sort By
- OliverScheurichGold Contributor
=SUMIF($C$2:$C$11,B14,$D$2:$D$11)
If Material is the only criteria you can use this formula for the data layout of the example.
=SUMPRODUCT(($A14=$C$2:$C$11)*(B$13=$B$2:$B$11)*$D$2:$D$11)
For 2 criteria you can apply SUMPRODUCT.
=SUMIFS($D$2:$D$11,$A$2:$A$11,F2,$B$2:$B$11,G2,$C$2:$C$11,H2)
For all 3 criteria you can make a unique list of Delivery Date, Parts and Material and then use SUMIFS.
- smikes181Copper ContributorMark, Thank You very much!!! I will have to read up on those 3. It is confusing in that you are not need to call out the part type or the material. Again, Thank you