Count formula

[mjmoon]

I want to use the countif function to count data in A1..A50 if = "Boats" and if the data in column C1..C50 = "SeaRay". I know how to use the countif function for one condition but I have no idead how to use for two conditon as I have explained above. The end result should tell me how many SeaRay Boats I have on inventory along with all the other types I have.

Thank you in advance!
Marc

 

[Richard Schollar]

Hi Marc

Use Sumproduct:

=SUMPRODUCT((A1:A50="Boats")+0,(C1:C50="SeaRay")+0)

you can replace the criteria (ie "Boats", "searay") with cell references holding these values.

 

[mjmoon]

Thank you. What does the plus 0 mean and the end of each formula?

I also need a formula to sum the data in D1..D50 if A1..A50 = Boats and if C1..C50 = Sea Ray. Any suggestions?

Marc

 

[Richard Schollar]

=SUMPRODUCT((A1:A50="Boats")+0,(C1:C50="SeaRay")+0,D1:D50)

The +0 coerces the generated list of True/Falses to 1s/0s so that they can be used in mathematical operations. You may also see *1 and -- used instead of +0. Another alternative is to carry out the coercion implicitly by multiplying all the terms together:

=SUMPRODUCT((A1:A50="Boats")*(C1:C50="SeaRay")*D1:D50)