Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use COUNTIF for 2 separate IF queries?
I'm trying to count the total number of successful changes within a given
timeframe but I either get a 'value' return, or it says I'm adding too many queries. I want to count each entry with the text 'success' in one column within a 7 day range. So column 'x' will be a date, and column 'y' will either say 'success' 'withdrawn' or 'refused'. So far I've tried: =IF(AND('RFCs in Progress'!Y2:Y502,"success",'RFCs in Progress'!X2:X502,"28/12/2008"),"1","0") but that produces a 'value' result rather than the total number so it must be wrong. I've also tried =AND(countif,'RFCs in Progress'!X2:X58028/12/2008,'RFCs in Progress'!Y2:Y580,"success") but that gives me a 'name' result so must also be wrong. Is it because I'm comparing too many things or because I'm trying to use dates? Any help greatly appreciated I've been trying to do this for ages! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use COUNTIF for 2 separate IF queries?
=SUMPRODUCT(--('RFCs in Progress'!X2:X580--"2008-12-28"),
--('RFCs in Progress'!Y2:Y580="success") -- __________________________________ HTH Bob "Kerry" wrote in message ... I'm trying to count the total number of successful changes within a given timeframe but I either get a 'value' return, or it says I'm adding too many queries. I want to count each entry with the text 'success' in one column within a 7 day range. So column 'x' will be a date, and column 'y' will either say 'success' 'withdrawn' or 'refused'. So far I've tried: =IF(AND('RFCs in Progress'!Y2:Y502,"success",'RFCs in Progress'!X2:X502,"28/12/2008"),"1","0") but that produces a 'value' result rather than the total number so it must be wrong. I've also tried =AND(countif,'RFCs in Progress'!X2:X58028/12/2008,'RFCs in Progress'!Y2:Y580,"success") but that gives me a 'name' result so must also be wrong. Is it because I'm comparing too many things or because I'm trying to use dates? Any help greatly appreciated I've been trying to do this for ages! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use COUNTIF for 2 separate IF queries?
You can only use one condition in COUNTIF - use SUMPRODUCT instead,
like this: =SUMPRODUCT(('RFCs in Progress'!X2:X580--"28/12/2008")*('RFCs in Progress'!Y2:Y580="success")) Might be better to put the date in a separate cell, eg D1, and then refer to that in the formula: =SUMPRODUCT(('RFCs in Progress'!X2:X580D1)*('RFCs in Progress'! Y2:Y580="success")) then you don't need to modify the formula to try it with different dates. Hope this helps. Pete On Jan 15, 5:56*pm, Kerry wrote: I'm trying to count the total number of successful changes within a given timeframe but I either get a 'value' return, or it says I'm adding too many queries. *I want to count each entry with the text 'success' in one column within a 7 day range. *So column 'x' will be a date, and column 'y' will either say 'success' 'withdrawn' or 'refused'. *So far I've tried: *=IF(AND('RFCs in Progress'!Y2:Y502,"success",'RFCs in Progress'!X2:X502,"28/12/2008"),"1","0") but that produces a 'value' result rather than the total number so it must be wrong. *I've also tried =AND(countif,'RFCs in Progress'!X2:X58028/12/2008,'RFCs in Progress'!Y2:Y580,"success") but that gives me a 'name' result so must also be wrong. *Is it because I'm comparing too many things or because I'm trying to use dates? *Any help greatly appreciated I've been trying to do this for ages! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use COUNTIF for 2 separate IF queries?
Thanks guys. Bob, your formula almost works but not quite. The formula is
returning a result of 51 when it should be 43. I'm trying to find all successful outcomes within a 7 day period and will need to do this for each week. So I'll need all successful changes between the date range of say, 29/12/08 to 04/01/09. I assumed using the 'greater than' function would be best but maybe not. "Bob Phillips" wrote: =SUMPRODUCT(--('RFCs in Progress'!X2:X580--"2008-12-28"), --('RFCs in Progress'!Y2:Y580="success") -- __________________________________ HTH Bob "Kerry" wrote in message ... I'm trying to count the total number of successful changes within a given timeframe but I either get a 'value' return, or it says I'm adding too many queries. I want to count each entry with the text 'success' in one column within a 7 day range. So column 'x' will be a date, and column 'y' will either say 'success' 'withdrawn' or 'refused'. So far I've tried: =IF(AND('RFCs in Progress'!Y2:Y502,"success",'RFCs in Progress'!X2:X502,"28/12/2008"),"1","0") but that produces a 'value' result rather than the total number so it must be wrong. I've also tried =AND(countif,'RFCs in Progress'!X2:X58028/12/2008,'RFCs in Progress'!Y2:Y580,"success") but that gives me a 'name' result so must also be wrong. Is it because I'm comparing too many things or because I'm trying to use dates? Any help greatly appreciated I've been trying to do this for ages! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use COUNTIF for 2 separate IF queries?
=SUMPRODUCT(--('RFCs in Progress'!X2:X580=--"2008-12-28"),
--('RFCs in Progress'!X2:X580<=--"2009-01-04"), --('RFCs in Progress'!Y2:Y580="success") but it is better to put the test dates in cells and check against the cell refreences. -- __________________________________ HTH Bob "Kerry" wrote in message ... Thanks guys. Bob, your formula almost works but not quite. The formula is returning a result of 51 when it should be 43. I'm trying to find all successful outcomes within a 7 day period and will need to do this for each week. So I'll need all successful changes between the date range of say, 29/12/08 to 04/01/09. I assumed using the 'greater than' function would be best but maybe not. "Bob Phillips" wrote: =SUMPRODUCT(--('RFCs in Progress'!X2:X580--"2008-12-28"), --('RFCs in Progress'!Y2:Y580="success") -- __________________________________ HTH Bob "Kerry" wrote in message ... I'm trying to count the total number of successful changes within a given timeframe but I either get a 'value' return, or it says I'm adding too many queries. I want to count each entry with the text 'success' in one column within a 7 day range. So column 'x' will be a date, and column 'y' will either say 'success' 'withdrawn' or 'refused'. So far I've tried: =IF(AND('RFCs in Progress'!Y2:Y502,"success",'RFCs in Progress'!X2:X502,"28/12/2008"),"1","0") but that produces a 'value' result rather than the total number so it must be wrong. I've also tried =AND(countif,'RFCs in Progress'!X2:X58028/12/2008,'RFCs in Progress'!Y2:Y580,"success") but that gives me a 'name' result so must also be wrong. Is it because I'm comparing too many things or because I'm trying to use dates? Any help greatly appreciated I've been trying to do this for ages! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use COUNTIF for 2 separate IF queries?
OK thanks Bob. Will give both a go. Many thanks.
"Bob Phillips" wrote: =SUMPRODUCT(--('RFCs in Progress'!X2:X580=--"2008-12-28"), --('RFCs in Progress'!X2:X580<=--"2009-01-04"), --('RFCs in Progress'!Y2:Y580="success") but it is better to put the test dates in cells and check against the cell refreences. -- __________________________________ HTH Bob "Kerry" wrote in message ... Thanks guys. Bob, your formula almost works but not quite. The formula is returning a result of 51 when it should be 43. I'm trying to find all successful outcomes within a 7 day period and will need to do this for each week. So I'll need all successful changes between the date range of say, 29/12/08 to 04/01/09. I assumed using the 'greater than' function would be best but maybe not. "Bob Phillips" wrote: =SUMPRODUCT(--('RFCs in Progress'!X2:X580--"2008-12-28"), --('RFCs in Progress'!Y2:Y580="success") -- __________________________________ HTH Bob "Kerry" wrote in message ... I'm trying to count the total number of successful changes within a given timeframe but I either get a 'value' return, or it says I'm adding too many queries. I want to count each entry with the text 'success' in one column within a 7 day range. So column 'x' will be a date, and column 'y' will either say 'success' 'withdrawn' or 'refused'. So far I've tried: =IF(AND('RFCs in Progress'!Y2:Y502,"success",'RFCs in Progress'!X2:X502,"28/12/2008"),"1","0") but that produces a 'value' result rather than the total number so it must be wrong. I've also tried =AND(countif,'RFCs in Progress'!X2:X58028/12/2008,'RFCs in Progress'!Y2:Y580,"success") but that gives me a 'name' result so must also be wrong. Is it because I'm comparing too many things or because I'm trying to use dates? Any help greatly appreciated I've been trying to do this for ages! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple ? countif, 3 separate conditions across multi tabs | Excel Worksheet Functions | |||
COUNTIF for criteria in two separate columns (Excel 2003) | Excel Worksheet Functions | |||
can countif function look at 2 separate columns for criteria | Excel Worksheet Functions | |||
Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) | |||
How do I set up an array using countif for 2 separate arguments. | Excel Worksheet Functions |