Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help w/ counting multiple columns based on IF criteria
I am trying to figure out how to count an occurence if a condition in one
column is true and if a condition in another column is true as well. Here is an example: A B C D Subdivision Presold Spec Model BANKER'S HAVEN x INVESTOR'S HOLLOW x BUILDER'S ESTATES x I want to count everytime the subdivision = "Banker's Haven" and "Presold". I have racked my brain and tried everything. part of the problem is that the information is provided by external clients and I do not have control of the format. It is likely that all of the information comes in on 1 tab and may have a couple thousand units. Can someone help me? I have tried nested IF formulas..IF AND formulas which doesn't seem to work b/c the comparison data is in two seperate columns. I've tried SUMIF, COUNTIF, you name it..Anyone have any solutions? Thanks for your help. James |
#2
|
|||
|
|||
Help w/ counting multiple columns based on IF criteria
Assuming Presold is signified by an "x" in column B, then
=sumproduct(--(a2:a1000="Banker's Haven"),--(b2:B1000="x")) "ConstructionGuy" wrote: I am trying to figure out how to count an occurence if a condition in one column is true and if a condition in another column is true as well. Here is an example: A B C D Subdivision Presold Spec Model BANKER'S HAVEN x INVESTOR'S HOLLOW x BUILDER'S ESTATES x I want to count everytime the subdivision = "Banker's Haven" and "Presold". I have racked my brain and tried everything. part of the problem is that the information is provided by external clients and I do not have control of the format. It is likely that all of the information comes in on 1 tab and may have a couple thousand units. Can someone help me? I have tried nested IF formulas..IF AND formulas which doesn't seem to work b/c the comparison data is in two seperate columns. I've tried SUMIF, COUNTIF, you name it..Anyone have any solutions? Thanks for your help. James |
#3
|
|||
|
|||
Help w/ counting multiple columns based on IF criteria
Duke--you are awesome! Thanks a million!
James "Duke Carey" wrote: Assuming Presold is signified by an "x" in column B, then =sumproduct(--(a2:a1000="Banker's Haven"),--(b2:B1000="x")) "ConstructionGuy" wrote: I am trying to figure out how to count an occurence if a condition in one column is true and if a condition in another column is true as well. Here is an example: A B C D Subdivision Presold Spec Model BANKER'S HAVEN x INVESTOR'S HOLLOW x BUILDER'S ESTATES x I want to count everytime the subdivision = "Banker's Haven" and "Presold". I have racked my brain and tried everything. part of the problem is that the information is provided by external clients and I do not have control of the format. It is likely that all of the information comes in on 1 tab and may have a couple thousand units. Can someone help me? I have tried nested IF formulas..IF AND formulas which doesn't seem to work b/c the comparison data is in two seperate columns. I've tried SUMIF, COUNTIF, you name it..Anyone have any solutions? Thanks for your help. James |
#4
|
|||
|
|||
Help w/ counting multiple columns based on IF criteria
Hi,
You may also try this array formula (Ctrl+Shift+Enter) =sum(if((range1="BANKER'S HAVEN")*(range2="x"),1,0)) Regards, Ashish Mathur "ConstructionGuy" wrote: I am trying to figure out how to count an occurence if a condition in one column is true and if a condition in another column is true as well. Here is an example: A B C D Subdivision Presold Spec Model BANKER'S HAVEN x INVESTOR'S HOLLOW x BUILDER'S ESTATES x I want to count everytime the subdivision = "Banker's Haven" and "Presold". I have racked my brain and tried everything. part of the problem is that the information is provided by external clients and I do not have control of the format. It is likely that all of the information comes in on 1 tab and may have a couple thousand units. Can someone help me? I have tried nested IF formulas..IF AND formulas which doesn't seem to work b/c the comparison data is in two seperate columns. I've tried SUMIF, COUNTIF, you name it..Anyone have any solutions? Thanks for your help. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) | |||
Counting multiple occurances of a specific string | Excel Worksheet Functions | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) |