Can I use COUNTIF for 2 separate IF queries?

[Kerry]

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:X580>28/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!

 

[Bob Phillips]

=SUMPRODUCT(--('RFCs in Progress'!X2:X580>--"2008-12-28"),
--('RFCs in Progress'!Y2:Y580="success")

 

[Pete_UK]

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:X580>D1)*('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

 

[Kerry]

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]

=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.

 

[Kerry]

OK thanks Bob. Will give both a go. Many thanks.