| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel-count a value in 1 column if a value in a 2nd is true | Excel Worksheet Functions | |||
| Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
| Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
| Count entries in one column based on values in another column | Excel Worksheet Functions | |||
| how 2sum values in column C, IF 'X' & 'Y' in colums A & B =TRUE | Excel Worksheet Functions | |||
Hybrid Mode
