| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIF-dual conditions
Hi,
I have two sets of data that I want to summarize in another worksheet in the same book. Here is the exact data: Worksheet Name: APAC_JAPAN LEARNING PARTNERS COL A COL B Theater Mid year APAC Yes APAC No APAC No APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No JAPAN No APAC No APAC Yes APAC Yes APAC No APAC No APAC No APAC No APAC No APAC Yes APAC No APAC Yes APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No Worksheet Name: Summary Mid-Year Review Status: APAC Japan Yes <ins forumula here <ins forumula here No <ins forumula here <ins forumula here I want to place a forumla in <ins forumla here which pulls from "APAC_JAPAN LEARNING PARTNERS" COL A (Theater) = APAC and COL (Mid Year) = Yes. I must pull the same data for Japan, and for the "No's". I can figure out how to edit the formula for my other needs once I get a good formula started :). I've been trying CountIF's, Subproducts, IF, Match, Find's, and can't seem to figure out the right combo. I'm sure it's easy but I'm new to excel formulas... Thanks! Julie |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIF-dual conditions
say your data is in A1:B100. then for APAC/yes try:
=SUM(IF((A1:A100="APAC")*(B1:B100="yes");1;0)) change appropriately for Japan, no and other combinations this is an array formula taht should be entered with CTRL+SHIFT+ENTER instead of ENTER alone "Julie" wrote: Hi, I have two sets of data that I want to summarize in another worksheet in the same book. Here is the exact data: Worksheet Name: APAC_JAPAN LEARNING PARTNERS COL A COL B Theater Mid year APAC Yes APAC No APAC No APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No JAPAN No APAC No APAC Yes APAC Yes APAC No APAC No APAC No APAC No APAC No APAC Yes APAC No APAC Yes APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No Worksheet Name: Summary Mid-Year Review Status: APAC Japan Yes <ins forumula here <ins forumula here No <ins forumula here <ins forumula here I want to place a forumla in <ins forumla here which pulls from "APAC_JAPAN LEARNING PARTNERS" COL A (Theater) = APAC and COL (Mid Year) = Yes. I must pull the same data for Japan, and for the "No's". I can figure out how to edit the formula for my other needs once I get a good formula started :). I've been trying CountIF's, Subproducts, IF, Match, Find's, and can't seem to figure out the right combo. I'm sure it's easy but I'm new to excel formulas... Thanks! Julie |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
change ; to ,
"Julie" wrote: Hi, I have two sets of data that I want to summarize in another worksheet in the same book. Here is the exact data: Worksheet Name: APAC_JAPAN LEARNING PARTNERS COL A COL B Theater Mid year APAC Yes APAC No APAC No APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No JAPAN No APAC No APAC Yes APAC Yes APAC No APAC No APAC No APAC No APAC No APAC Yes APAC No APAC Yes APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No Worksheet Name: Summary Mid-Year Review Status: APAC Japan Yes <ins forumula here <ins forumula here No <ins forumula here <ins forumula here I want to place a forumla in <ins forumla here which pulls from "APAC_JAPAN LEARNING PARTNERS" COL A (Theater) = APAC and COL (Mid Year) = Yes. I must pull the same data for Japan, and for the "No's". I can figure out how to edit the formula for my other needs once I get a good formula started :). I've been trying CountIF's, Subproducts, IF, Match, Find's, and can't seem to figure out the right combo. I'm sure it's easy but I'm new to excel formulas... Thanks! Julie |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIF-dual conditions
change ; to ,
SORRY "Julie" wrote: Hi, I have two sets of data that I want to summarize in another worksheet in the same book. Here is the exact data: Worksheet Name: APAC_JAPAN LEARNING PARTNERS COL A COL B Theater Mid year APAC Yes APAC No APAC No APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No JAPAN No APAC No APAC Yes APAC Yes APAC No APAC No APAC No APAC No APAC No APAC Yes APAC No APAC Yes APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No Worksheet Name: Summary Mid-Year Review Status: APAC Japan Yes <ins forumula here <ins forumula here No <ins forumula here <ins forumula here I want to place a forumla in <ins forumla here which pulls from "APAC_JAPAN LEARNING PARTNERS" COL A (Theater) = APAC and COL (Mid Year) = Yes. I must pull the same data for Japan, and for the "No's". I can figure out how to edit the formula for my other needs once I get a good formula started :). I've been trying CountIF's, Subproducts, IF, Match, Find's, and can't seem to figure out the right combo. I'm sure it's easy but I'm new to excel formulas... Thanks! Julie |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIF-dual conditions
Hi,
Thanks :). I still get an error...not sure why. The error message isn't specific but directs me to the Help menu. Can I select Theater Column entirely by A:A and Mid Year columb B:B? Here is the formula (which directs to the appropriate tab/column range in my workbook): =SUM(IF(('APAC_JAPAN LEARNING PARTNERS'!A:A="APAC")*('APAC_JAPAN LEARNING PARTNERS'!B:B="yes");1;0)) I will be adding rows as I go so don't want to limit the search range... Thanks, Julie "Jarek Kujawa" wrote: say your data is in A1:B100. then for APAC/yes try: =SUM(IF((A1:A100="APAC")*(B1:B100="yes");1;0)) change appropriately for Japan, no and other combinations this is an array formula taht should be entered with CTRL+SHIFT+ENTER instead of ENTER alone "Julie" wrote: Hi, I have two sets of data that I want to summarize in another worksheet in the same book. Here is the exact data: Worksheet Name: APAC_JAPAN LEARNING PARTNERS COL A COL B Theater Mid year APAC Yes APAC No APAC No APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No JAPAN No APAC No APAC Yes APAC Yes APAC No APAC No APAC No APAC No APAC No APAC Yes APAC No APAC Yes APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No Worksheet Name: Summary Mid-Year Review Status: APAC Japan Yes <ins forumula here <ins forumula here No <ins forumula here <ins forumula here I want to place a forumla in <ins forumla here which pulls from "APAC_JAPAN LEARNING PARTNERS" COL A (Theater) = APAC and COL (Mid Year) = Yes. I must pull the same data for Japan, and for the "No's". I can figure out how to edit the formula for my other needs once I get a good formula started :). I've been trying CountIF's, Subproducts, IF, Match, Find's, and can't seem to figure out the right combo. I'm sure it's easy but I'm new to excel formulas... Thanks! Julie |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIF-dual conditions
It multiplied the two data sets after I referenced it to the other sheet:
=SUM(IF(('APAC_JAPAN LEARNING PARTNERS'!A:A="APAC")*('APAC_JAPAN LEARNING PARTNERS'!B:B="yes"),1,0)) "Jarek Kujawa" wrote: say your data is in A1:B100. then for APAC/yes try: =SUM(IF((A1:A100="APAC")*(B1:B100="yes");1;0)) change appropriately for Japan, no and other combinations this is an array formula taht should be entered with CTRL+SHIFT+ENTER instead of ENTER alone "Julie" wrote: Hi, I have two sets of data that I want to summarize in another worksheet in the same book. Here is the exact data: Worksheet Name: APAC_JAPAN LEARNING PARTNERS COL A COL B Theater Mid year APAC Yes APAC No APAC No APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No JAPAN No APAC No APAC Yes APAC Yes APAC No APAC No APAC No APAC No APAC No APAC Yes APAC No APAC Yes APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No Worksheet Name: Summary Mid-Year Review Status: APAC Japan Yes <ins forumula here <ins forumula here No <ins forumula here <ins forumula here I want to place a forumla in <ins forumla here which pulls from "APAC_JAPAN LEARNING PARTNERS" COL A (Theater) = APAC and COL (Mid Year) = Yes. I must pull the same data for Japan, and for the "No's". I can figure out how to edit the formula for my other needs once I get a good formula started :). I've been trying CountIF's, Subproducts, IF, Match, Find's, and can't seem to figure out the right combo. I'm sure it's easy but I'm new to excel formulas... Thanks! Julie |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIF-dual conditions
1. of course you are right - I forgot about adding 'APAC_JAPAN LEARNING
PARTNERS' to the formula 2. you can reference as A:A or B:B 3. don't know about multiplication, summing works fine on my Excel 2003 "Julie" wrote: It multiplied the two data sets after I referenced it to the other sheet: =SUM(IF(('APAC_JAPAN LEARNING PARTNERS'!A:A="APAC")*('APAC_JAPAN LEARNING PARTNERS'!B:B="yes"),1,0)) "Jarek Kujawa" wrote: say your data is in A1:B100. then for APAC/yes try: =SUM(IF((A1:A100="APAC")*(B1:B100="yes");1;0)) change appropriately for Japan, no and other combinations this is an array formula taht should be entered with CTRL+SHIFT+ENTER instead of ENTER alone "Julie" wrote: Hi, I have two sets of data that I want to summarize in another worksheet in the same book. Here is the exact data: Worksheet Name: APAC_JAPAN LEARNING PARTNERS COL A COL B Theater Mid year APAC Yes APAC No APAC No APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No JAPAN No APAC No APAC Yes APAC Yes APAC No APAC No APAC No APAC No APAC No APAC Yes APAC No APAC Yes APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No Worksheet Name: Summary Mid-Year Review Status: APAC Japan Yes <ins forumula here <ins forumula here No <ins forumula here <ins forumula here I want to place a forumla in <ins forumla here which pulls from "APAC_JAPAN LEARNING PARTNERS" COL A (Theater) = APAC and COL (Mid Year) = Yes. I must pull the same data for Japan, and for the "No's". I can figure out how to edit the formula for my other needs once I get a good formula started :). I've been trying CountIF's, Subproducts, IF, Match, Find's, and can't seem to figure out the right combo. I'm sure it's easy but I'm new to excel formulas... Thanks! Julie |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CountIF-dual conditions
Your formula looks OK. You have to commit with ctrl+shift+enter to execute,
not just enter "Julie" wrote: It multiplied the two data sets after I referenced it to the other sheet: =SUM(IF(('APAC_JAPAN LEARNING PARTNERS'!A:A="APAC")*('APAC_JAPAN LEARNING PARTNERS'!B:B="yes"),1,0)) "Jarek Kujawa" wrote: say your data is in A1:B100. then for APAC/yes try: =SUM(IF((A1:A100="APAC")*(B1:B100="yes");1;0)) change appropriately for Japan, no and other combinations this is an array formula taht should be entered with CTRL+SHIFT+ENTER instead of ENTER alone "Julie" wrote: Hi, I have two sets of data that I want to summarize in another worksheet in the same book. Here is the exact data: Worksheet Name: APAC_JAPAN LEARNING PARTNERS COL A COL B Theater Mid year APAC Yes APAC No APAC No APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No JAPAN No APAC No APAC Yes APAC Yes APAC No APAC No APAC No APAC No APAC No APAC Yes APAC No APAC Yes APAC No APAC No APAC No JAPAN No JAPAN No JAPAN No Worksheet Name: Summary Mid-Year Review Status: APAC Japan Yes <ins forumula here <ins forumula here No <ins forumula here <ins forumula here I want to place a forumla in <ins forumla here which pulls from "APAC_JAPAN LEARNING PARTNERS" COL A (Theater) = APAC and COL (Mid Year) = Yes. I must pull the same data for Japan, and for the "No's". I can figure out how to edit the formula for my other needs once I get a good formula started :). I've been trying CountIF's, Subproducts, IF, Match, Find's, and can't seem to figure out the right combo. I'm sure it's easy but I'm new to excel formulas... Thanks! Julie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF with two conditions | Excel Discussion (Misc queries) | |||
| Countif with conditions | Excel Worksheet Functions | |||
| Using countif with 2 conditions | Excel Worksheet Functions | |||
| COUNTIF for 2 conditions | Excel Worksheet Functions | |||
| COUNTIF with two conditions | Excel Worksheet Functions | |||
Linear Mode
