Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Formulas
I need help with creating a formula. I want to count the total number of individuals who meet a certain criteria in one column and a certain criteria in another column. For example: I want to find out the total number of people who live in Germany and went on a specific trip. For argument sake let's say column A is at lists only Y or N for yes and no, and column B is a list of Countries such as Germany, America, etc. I would like to figure out the total number of individuals who have a Y in column A and the word Germany in column B. This is what I have done so far and it works for counting only those with Germany in column B or a Y in column A: =COUNTIF('[Spreadsheet Name.xls]'!$B:$B,"Germany") or =COUNTIF('[Spreadsheet Name.xls]'!$A:$A,"Y") The first formula will give me the total number of individuals with the word Germany in column B, and the second will give me a total number of individuals with a Y in column A. How can I combine these two formulas to come up with one that will only count those individuals who have both the word Germany in column B and a Y in column A? I appreciate your assistance with this matter. Please respond to Thanks, Fritz -- fritzj8 ------------------------------------------------------------------------ fritzj8's Profile: http://www.excelforum.com/member.php...o&userid=27167 View this thread: http://www.excelforum.com/showthread...hreadid=466772 |
#2
|
|||
|
|||
=SUMPRODUCT(--(A1:A100=\"Y\"),--(B1:B100=\"GERMANY\")) is your formula and next time, it is not necessary to multipost whatever problem you have. Regards. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=466772 |
#3
|
|||
|
|||
fritzj8 Wrote: I need help with creating a formula. I want to count the total number of individuals who meet a certain criteria in one column and a certain criteria in another column. For example: I want to find out the total number of people who live in Germany and went on a specific trip. For argument sake let's say column A is at lists only Y or N for yes and no, and column B is a list of Countries such as Germany, America, etc. I would like to figure out the total number of individuals who have a Y in column A and the word Germany in column B. This is what I have done so far and it works for counting only those with Germany in column B or a Y in column A: =COUNTIF('[Spreadsheet Name.xls]'!$B:$B,"Germany") or =COUNTIF('[Spreadsheet Name.xls]'!$A:$A,"Y") The first formula will give me the total number of individuals with the word Germany in column B, and the second will give me a total number of individuals with a Y in column A. How can I combine these two formulas to come up with one that will only count those individuals who have both the word Germany in column B and a Y in column A? I appreciate your assistance with this matter. Please respond to Thanks, Fritz Hi Fritz Try this =SUM((A1:A15="Y")*(B1:B15="Germany")) This needs to be enterred as an array, so type in the formula and then press CTRL/Shift/Enter all together Change the ranges to suit your data -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=466772 |
#4
|
|||
|
|||
Thanks BenjieLop that did it. Now all I need help with figuring out is how to count only those fields that have a date in them. For example: Column C has various dates listed and some of the entries are just text such as 12-Sep-05 or the words Did Not Go. However, all I need to count are only those fields with a date in the block and not text. Any ideas? -- fritzj8 ------------------------------------------------------------------------ fritzj8's Profile: http://www.excelforum.com/member.php...o&userid=27167 View this thread: http://www.excelforum.com/showthread...hreadid=466772 |
#5
|
|||
|
|||
fritzj8 Wrote: Thanks BenjieLop that did it. Now all I need help with figuring out is how to count only those fields that have a date in them. For example: Column C has various dates listed and some of the entries are just text such as 12-Sep-05 or the words Did Not Go. However, all I need to count are only those fields with a date in the block and not text. Any ideas? If I understand you correctly, column C entries are either dates or the words "Did Not Go" and I will assume all the Column C cells are "general" formatted. Your formula will then be =COUNTIF(C1:C100,\"<DID NOT GO\") Regards. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=466772 |
#6
|
|||
|
|||
Yes column C is either a date or text, and no the cells are not "general" formatted but rather "date" formatted. It is just that some of them have text rather than a date. I only want to count the fields with an actual date in them for example: Column C 24-Aug-05 No Show 01-Sep-05 12-Sep-05 No Show I want my formula to count just the fields with a date in it and give me a total, which in this example is three. That way I know only three people attended. I do not need to count the fields with text or nothing in them. Thanks, Fritz -- fritzj8 ------------------------------------------------------------------------ fritzj8's Profile: http://www.excelforum.com/member.php...o&userid=27167 View this thread: http://www.excelforum.com/showthread...hreadid=466772 |
#7
|
|||
|
|||
The formula that I gave you will still work then. Simply change "Did Not Go" to "No Show" in the formula and you will be OK. Regards. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=466772 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
Formulas | Excel Worksheet Functions | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |