CODEWITHSUNDEEP
excelcounting
user3111407
user3111407

Reputation: 1

Counting number of rows a value occurs in across a range of columns

This is my first question so hopefully I'll manage to make sense.

I have the following spreadsheet. In the Column A is the Point code, Column B is Record A and Column C is Record B. The point code is just free text and then Record A and B have one of the following values, Absent, R, O, F, A or D. I want to find all the points (i.e. rows) that have D in either column B or C. Some rows may have no D, some may have D in one column and some may have it in both columns.

How do I do this? I need to do it without making any extra columns, i.e converting all the D to 1's and then summing the two columns and counting anything >0.

Thanks.

Upvotes: 0

Views: 282

Answers (1)

barry houdini
barry houdini

Reputation: 46451

Try using SUMPRODUCT, e.g.

=SUMPRODUCT(((B2:B1000="D")+(C2:C1000="D")>0)+0)

That will count any row that has 1 or 2 "D"s, adjust ranges as required

Upvotes: 1

Related Questions