=SUMPRODUCT(($A$1:$A$14="Book")*($B$1:$B$14="Londo n"))+SUMPRODUCT(($A$1:$A$14="Magazine")*($B$1:$B$1 4="Oxford"))

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"MikeB" wrote:

I think I might have fixed it myself with:

=SUMPRODUCT((A2:A1000="BOOK")*(B2:B1000="LONDON"))

"MikeB" wrote:

OK,

I have a spreadsheet with a large amount of data but want to create a
formula based on just two of the columns both containing text values as
follows:

BOOK LONDON
BOOK NEWYORK
BOOK LONDON
BOOK LONDON
BOOK LONDON
BOOK OXFORD
MAGAZINE LONDON
MAGAZINE LONDON
MAGAZINE OXFORD
MAGAZINE OXFORD
MAGAZINE OXFORD

and so on....

I need a formula which will count the number of "BOOK" in "LONDON" or
MAGAZINE in OXFORD.

Hence I need something that basically has the logic:

If B1 = LONDON and A1 = BOOK, Count it.....and then perform the same again
on each row.

=COUNTIF(A1:A100,"BOOK") will count the number of times BOOK appears in
column A but how do I take the next step and only count it if the
corresponding value in column B is LONDON etc?

Thanks in advance