Count If

[bladeuk]

Hi All,
I know this has to be possible but simply can't think straight today........

I have two columns with text in.

I want to count up the instances wher the text is "Other" in one column and "MANOR" in the other.

Other and MANOR may appear a number of times but not together as it were.

eg

Other MANOR
Other ROAD
Other MANOR
None MANOR

I would want the count to return 2

Hope this makes sense and thanks for any help

Dave

 

[njimack]

=SUMPRODUCT(--(A1:A100="Other"),--(B1:B100="MANOR"))

 

[bladeuk]

Sound !! I tried AA:AA etc to get the complete column.....but this didn't work..any ideas?

Many thanks again

Dave

 

[DonkeyOte]

pre XL07 you can't use entire columns in a sumproduct -- revise to A1:A65000 or whatever but with sumproduct try to keep ranges as small as they can be -- if last row of data is in row 10000 use 10000 (or say 12500 for leeway)

EDIT: also each range used must be of the same size -- so if you're looking at columns A & B both ranges must reference the same rows (ie 1:10000) can't have A1:A1000 and B1:B100

 

[njimack]

SUMPRODUCT doesn't accept entire columns as ranges (unless you're using Excel 2007). The preferred approach is to use dynamic named ranges - take a look at http://www.contextures.com/xlNames01.html#Dynamic

 

[bladeuk]

Thanks both

Very helpful

Dave