countif

  • Thread starter Thread starter Andy K
  • Start date Start date
A

Andy K

Cells A1: A10 and B1:B10 have a list of components "NUTS" and "BOLTS" I
would like to use countif to total the number of times I see NUTS but only if
bolts can be seen in the same row
A B
1 NUTS BOLTS 1
2 SCREWS BOLTS
3 NUTS WASHERS
4 NUTS WASHERS
5 SCREWS BOLTS
6 SCREWS WASHERS
7 SCREWS WASHERS
8 NUTS WASHERS
9 SCREWS WASHERS
10 NUTS BOLTS 1
Total 2

Thanks in advance
 
=SUMPRODUCT(--(A2:A11="NUTS"),--(B2:B11="BOLTS"))
Regards,
Stefi

„Andy K†ezt írta:
 
=SUMPRODUCT(($A$1:$A$100="Nuts")*($B$1:$B$100="Bolts"))

If this post helps click Yes
 
What do you get from the formulae =A1="NUTS" and =B1="BOLTS" ?
Perhaps you have extra spaces (or other non-printing characters) in the
cells (although your example didn't have)?
What do you get from the formulae =LEN(A1) and =LEN(B1) ?
 
Back
Top