Forum Discussion

smikes181's avatar
smikes181
Copper Contributor
Jan 31, 2023
Solved

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

 

 

  • smikes181's avatar
    smikes181
    Feb 01, 2023
    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

  • smikes181 

    =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.


     

    • smikes181's avatar
      smikes181
      Copper Contributor
      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

Resources