Counting number of rows if at least one instance appears in one of three columns

[JustyR]

Hi, all,

Hoping someone can help me solve this.

I have a spreadsheet with four columns (columns are sessions). The rows are weeks. So, I'm using a bullet point to indicate when I've attended any one of up to four sessions in each week.

A B C D
1 • x x x
2 x • • x
3 x • x x
4 x x x x

I want to be able to count the number of rows that have at least one bullet point in A, B, C, or D.

So the formula would return a result of 3.

Is this possible?

Many thanks

 

[Joe4]

Are the x's really x's, or do they represent something else?
Whatever they are, will they always be the same (equal to each other) if they are not a bullet point?

 

[Rick Rothstein]

Assuming you did not "lie" to us about the total number of columns, this formula should work for you ...

=SUMPRODUCT(--ISNUMBER((FIND("•",A1:A4&B1:B4&C1:C4&D1:D4))))

 

[JustyR]

Assuming you did not "lie" to us about the total number of columns, this formula should work for you ...

=SUMPRODUCT(--ISNUMBER((FIND("•",A1:A4&B1:B4&C1:C4&D1:D4))))

That's the ticket!!!

Excellent. I was flailing around with countifs and wouldn't have got that. Many thanks. Works perfectly.