Count values in column only if value of another column is true

[MikeB]

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

 

[Mike H]

Try this

=SUMPRODUCT((A1:A10="Book")*(B1:B10="London"))

In practice i'd use cell references

=SUMPRODUCT((A1:A10=C1)*(B1:B10=D1))

Mike

 

[Jacob Skaria]

Try
=SUMPRODUCT(--(A2:A10="Book"),--(B2:B10="London"))

If this post helps click Yes

 

[MikeB]

I think I might have fixed it myself with:

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

 

[ryguy7272]

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

HTH,
Ryan--