countif

[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

 

[Stefi]

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

„Andy K†ezt írta:

 

[Jacob Skaria]

=SUMPRODUCT(($A$1:$A$100="Nuts")*($B$1:$B$100="Bolts"))

If this post helps click Yes

 

[David Biddulph]

Not COUNTIF, but:
=SUMPRODUCT((A1:A10="NUTS")*(B1:B10="BOLTS"))

 

[Andy K]

Thanks this is Just what I'm looking for

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

„Andy K†ezt írta:

 

[Andy K]

Result showing 0 not sure how I'm going wrong

Not COUNTIF, but:
=SUMPRODUCT((A1:A10="NUTS")*(B1:B10="BOLTS"))

 

[David Biddulph]

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) ?

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Andy K†ezt írta: