COUNTIF Function

[leimst]

Good Morning,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a scenario where I would like to “Approve” or “Decline” checks based on allowing 3 checks to be written in any 2 day rolling period. Consequently, I would like Excel to look at each row, check previous dates (assuming sheet is sorted by date) checking back as many rows as necessary and keep a running tally of how many checks have been written in the last 2 days. Then the “Approve” or “Decline” decision would be based on whether the Running Total was >3.<o></o>
<o> </o>
I’ve experimented with the COUNTIFS function but in this scenario, it would result in a “6” in the Running Total column for the 3/15 and 3/16 dates.<o></o>
<o> </o>
Criteria: 3 checks with any 2 day rolling period.<o></o>
<o></o>
Date Running Total Result Comments<o></o>
3/15 1 Approved<o></o>
3/15 2 Approved<o></o>
3/15 3 Approved<o></o>
3/15 4 Declined Only 3 checks allowed in 2 days<o></o>
3/16 5 Declined<o></o>
3/16 6 Declined<o></o>
3/17 1 Approved Apprvd since prev 2 on 3/16 Declined<o></o>
3/18 2 Approved<o></o>
3/19 2 Approved<o></o>
3/19 3 Approved<o></o>
3/20 3 Approved

Thanks in advance for any help!!

Brian

 

[AlphaFrog]

Excel Workbook
	A	B	C
1	Date*	Running Total**	Result
2	3-15	1	Approved
3	3-15	2	Approved
4	3-15	3	Approved
5	3-15	4	Declined
6	3-16	5	Declined
7	3-16	6	Declined
8	3-17	3	Approved
9	3-18	2	Approved
10	3-19	2	Approved
11	3-19	3	Approved
12	3-20	3	Approved
Sheet

 

[AlphaFrog]

Sorry. Disregard my previous post (#2).

This should be better

Excel Workbook
	A	B	C
1	Date*	Running Total**	Result
2	3-15	1	Approved
3	3-15	2	Approved
4	3-15	3	Approved
5	3-15	4	Declined
6	3-16	4	Declined
7	3-16	4	Declined
8	3-17	1	Approved
9	3-18	2	Approved
10	3-19	2	Approved
11	3-19	3	Approved
12	3-20	3	Approved
Sheet

If you have Excel 2007 or later, you could use the COUNTIFS function instead of SUMPRODUCT
Cell B2
=COUNTIFS($A1:A1, ">="&A2-1, $C$1:C1,"Approved")+1

 

[leimst]

I do have 2010 so I used the COUNTIFS function and it worked great!!! Thank you very much! I've looked at that til my head hurt and your solution seemed quite simple.

Thanks again!