Another count rows question.

[elchueco]

Totally stumped here.

I have a table which contains records in each row.
<table>
<tr><td></td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr>
<tr><td>1</td><td>Name</td><td>Item1</td><td>Item2</td><td>Item3</td><td>Item4</td></tr>
<tr><td>2</td><td>Bob</td><td>Book</td><td>Shirt</td><td>Book</td><td>Pen</td></tr>
<tr><td>3</td><td>Joe</td><td>Pen</td><td>Shoe</td><td>[    ]</td><td>Ring</td></tr>
<tr><td>4</td><td>Ann</td><td>Book</td><td>[    ]</td><td>Shirt</td><td>Pen</td></tr>
<tr><td>5</td><td>Bill</td><td>Shoe</td><td>Shirt</td><td>Ring</td><td>Book</td></tr>
</table>

*Note: Some cells will be empty.

I want to count how many rows contain the value "Book". For the table above, the result should be "3".

I'll be using this formula for a table that spans about 30 columns and thousands of rows.

At first I used:

=COUNTIF(B2:E5,"Book")

but that counts every cell that contains the value "Book" giving me a result of "4", which is not what I'm looking for.

Thanks for any help!

 

[jim may]

Create a HelperColumn as I did...

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	Item 1	Item 2	Item 3	Item 4	HelperCol
2	Bob	Book	Shirt	Book	Pen	TRUE
3	Ted	Pen		Pen	Shirt	FALSE
4	Carol	Book	Shirt	Pen	Book	TRUE
5	Alice	Book	Book	Shirt	Shirt	TRUE
6						3	<< Total Rows with Book
7
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F2	=COUNTIF(A2:E2,"Book")>0
F3	=COUNTIF(A3:E3,"Book")>0
F4	=COUNTIF(A4:E4,"Book")>0
F5	=COUNTIF(A5:E5,"Book")>0
F6	=COUNTIF(F2:F5,TRUE)

 

[elchueco]

Hi, Jim.

Thanks for your help. Unfortunately, I cannot afford to use helper columns. I'm dealing with a large number (about 50?) of "Items" and I may need to query for each of those items.

Your proposed solution would require me to use ~50 helper columns. I suppose I could just bite the bullet and create the helper columns under another sheet... I'm hoping there's a less convoluted solution.

Any thoughts?

Thanks again.

 

[barry houdini]

You could use a single formula like this

=SUMPRODUCT((COUNTIF(OFFSET(B2:E2,ROW(B2:E5)-ROW(B2),0),"Book")>0)+0)

 

[elchueco]

Hooray! Thanks Barry! This absolutely works for me.

The logic behind it totally goes over my head here, though. Would it be too much to ask for an explanation? In particular, I don't see how to offset function operates there.

Best regards

 

[barry houdini]

This part

ROW(B2:E5)-ROW(B2)

returns an array like this

{0;1;2;3}

so in the OFFSET function you get

=OFFSET(B2:E2,{0;1;2;3},0)

which supplies an array of ranges, one for each row of your whole range

....and then COUNTIF is applied to each row to count "Book".....and then we look at how many of those results are greater than zero (i.e. rows with at least one "book")....and SUMPRODUCT then counts the rows......

 

[elchueco]

thanks for the excellent explanation. makes sense to me now.

cheers!