Hi all,

Column A, rows 2-99, contains the number of bedrooms a house contains, e.g.
1, 2, 3 or 4.
Column B, rows 2-99, contains the number of storeys the house is, e.g. 1, 2,
3, 4

What formula would I use to count the nuber of 2 bedroom houses that are 3
storeys tall? =COUNTIF(A2:A99, AND???..

Please help, thank-you.

Kinf regards,

Neil

Homework? Look in the archives or google for SUMPRODUCT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Neil Pearce" wrote in message
...
Hi all,

Column A, rows 2-99, contains the number of bedrooms a house contains,
e.g.
1, 2, 3 or 4.
Column B, rows 2-99, contains the number of storeys the house is, e.g. 1,
2,
3, 4

What formula would I use to count the nuber of 2 bedroom houses that are 3
storeys tall? =COUNTIF(A2:A99, AND???..

Please help, thank-you.

Kinf regards,

Neil

Maybe

=SUMPRODUCT((A2:A99=2)*(B2:B99=3))

Mike

"Neil Pearce" wrote:

Hi all,

Column A, rows 2-99, contains the number of bedrooms a house contains, e.g.
1, 2, 3 or 4.
Column B, rows 2-99, contains the number of storeys the house is, e.g. 1, 2,
3, 4

What formula would I use to count the nuber of 2 bedroom houses that are 3
storeys tall? =COUNTIF(A2:A99, AND???..

Please help, thank-you.

Kinf regards,

Neil

Thank-you.

If I wanted the cell to list the contents of those cells that match the
criteria how would I go about this?

e.g.

Column A, rows 1-99, lists house numbers: Output of 1-99
Column B, rows 1-99, lists the houses' plot numbers: Output of either 1-10
Column C, rows 1-99, lists the phase that the houses fall under: Output
either 1,2,or 3

I now wish to create a table with plots on the left columns, phases on the
top row, adn the cells to list which houses are matching on both counts.

Inputting the formula provided inputs the number of houses in matchign both.
Now I wish the cell to list the houses and not just count them.

Is this possible?


Thanks in advance,

Neil


"Mike H" wrote:

Maybe

=SUMPRODUCT((A2:A99=2)*(B2:B99=3))

Mike

"Neil Pearce" wrote:

Hi all,

Column A, rows 2-99, contains the number of bedrooms a house contains, e.g.
1, 2, 3 or 4.
Column B, rows 2-99, contains the number of storeys the house is, e.g. 1, 2,
3, 4

What formula would I use to count the nuber of 2 bedroom houses that are 3
storeys tall? =COUNTIF(A2:A99, AND???..

Please help, thank-you.

Kinf regards,

Neil