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

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

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

Try this

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

In practice i'd use cell references

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

Mike

"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

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

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

If this post helps click Yes
---------------
Jacob Skaria


"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

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

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

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

=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