Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with countif using two columns
Hi, the answer to my problem is pobably obvious but i need help. I have a database in which column B returns True or False values and column E returns true or false values . I would like to count the number of cells in which False appears in both columns in the same row, ie if b4 and e4 are both false, then it returns the value 1. The range is from rows 3 to 383. I have tried these formulas but they keep on returning 0. =SUMPRODUCT((b3:b383="FALSE"),(E3:E383="FALSE")) =SUMPRODUCT((CZ3:CZ383="FALSE")*(E3:E383="FALSE")) . your help would be gratefully appreciated. -- Vipulparbat ------------------------------------------------------------------------ Vipulparbat's Profile: http://www.excelforum.com/member.php...o&userid=37683 View this thread: http://www.excelforum.com/showthread...hreadid=572897 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with countif using two columns
If those values that are returned are really the Boolean TRUE and FALSE, then
remove the double quotes: =SUMPRODUCT(--(b3:b383=FALSE),--(E3:E383=FALSE)) or =SUMPRODUCT((CZ3:CZ383=FALSE)*(E3:E383=FALSE)) But excel will treat empty cells as FALSE. You may want something like: =SUMPRODUCT(--(b3:b383<""),--(b3:b383=FALSE), --(e3:e383<""),--(e3:e383=FALSE)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Vipulparbat wrote: Hi, the answer to my problem is pobably obvious but i need help. I have a database in which column B returns True or False values and column E returns true or false values . I would like to count the number of cells in which False appears in both columns in the same row, ie if b4 and e4 are both false, then it returns the value 1. The range is from rows 3 to 383. I have tried these formulas but they keep on returning 0. =SUMPRODUCT((b3:b383="FALSE"),(E3:E383="FALSE")) =SUMPRODUCT((CZ3:CZ383="FALSE")*(E3:E383="FALSE")) . your help would be gratefully appreciated. -- Vipulparbat ------------------------------------------------------------------------ Vipulparbat's Profile: http://www.excelforum.com/member.php...o&userid=37683 View this thread: http://www.excelforum.com/showthread...hreadid=572897 -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with countif using two columns
Vipulparbat wrote:
Hi, the answer to my problem is pobably obvious but i need help. I have a database in which column B returns True or False values and column E returns true or false values . I would like to count the number of cells in which False appears in both columns in the same row, ie if b4 and e4 are both false, then it returns the value 1. The range is from rows 3 to 383. I have tried these formulas but they keep on returning 0. =SUMPRODUCT((b3:b383="FALSE"),(E3:E383="FALSE")) =SUMPRODUCT((CZ3:CZ383="FALSE")*(E3:E383="FALSE")) . your help would be gratefully appreciated. Try with this: =SUMPRODUCT((BZ3:BZ383=FALSE)*(E3:E383=FALSE)). -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with countif using two columns
Thanks Frank, I'm sure I tried that but it didnt work before. It works fine now -- Vipulparbat ------------------------------------------------------------------------ Vipulparbat's Profile: http://www.excelforum.com/member.php...o&userid=37683 View this thread: http://www.excelforum.com/showthread...hreadid=572897 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with countif using two columns
Vipulparbat wrote:
Thanks Frank, I'm sure I tried that but it didnt work before. It works fine now You're welcome. But... Who is Frank? -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL should let me establish unlimited numbers of columns. | Excel Discussion (Misc queries) | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Using COUNTIF to check values in multiple columns | Excel Worksheet Functions |