COOUNT IF statement with AND?

[gg]

Can I have a Count if "AND" statement? Together?

Countif(AND(.......?

Two criteria must be met before the count?

 

[NateO]

I am just trying to understand how I can apply it to the others.

3 in 4 days
4 in 5 days

???Any ideas?
{Snip}

See my function. Notice the >= criteria?

 

[gg]

If I could figure out how I would??....

 

[NateO]

You simply want to click on c1 in the 'spreadsheet.' No matter, here she is:

=SUMPRODUCT((A1:A13>=TODAY()-1)*(B1:B13="Fred"))

That's the last two days, today()-2 is 3, etc.... You can also add a third criteria to put a roof on your recordset.

 

[gg]

Nate thanks for your help.
I can get it to work for the Back to Back days.
What about if Fred occurs in 3 out of the last 4 days?

any idea?

 

[NateO]

You are welcome.

The logic is opaque eh? Try:

>=today()-3

Just use a boolean expresion, e.g., =3=sumproduct(blah blah)

 

[Yogi Anand]

Hi gg:

As Just_Jon has stated it would be nice to see you post some sample data. However, let me asume that your layout looks like the following ...

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	Dates	Names	Names2	Scores
2	01/07/2004	Jane	Kay	2					Score	Score	Score	Score
3	01/06/2004	Jane	Kay	3		Names	Names2	Status	today	yesterday	4In4Last5D	3In4Last4D
4	01/05/2004	Jane	Kay	4		Jane	kay	overworked	TRUE	TRUE	TRUE	TRUE
5	01/04/2004	Jane	Kay	5		Bob	Lutz	not_overworked	TRUE	FALSE	FALSE	FALSE
6	01/03/2004	Jane	Kay	3		Rich	Cong	overworked	TRUE	TRUE	TRUE	TRUE
7	01/02/2004	Jane	Kay	1		Bill	Cong	not_overworked	TRUE	FALSE	FALSE	FALSE
8	01/07/2004	Bob	Lutz	2
9	01/07/2004	Bill	Cong	3
10	01/02/2004	Rich	Cong	5
11	01/03/2004	Rich	Cong	5
12	01/04/2004	Rich	Cong	5
13	01/05/2004	Rich	Cong	5
14	01/06/2004	Rich	Cong	1
15	01/07/2004	Rich	Cong	5
Sheet5b

The formulas in cells I4:L4 are ...

=SUMPRODUCT((B2:B15=$F4)*(C2:C15=$G4)*(A2:A15=TODAY()))=1

=SUMPRODUCT((B2:B15=$F4)*(C2:C15=$G4)*(A2:A15=TODAY()-1)*(D2:D15>0))=1

=SUMPRODUCT((B2:B15=$F4)*(C2:C15=$G4)*(A2:A15>=TODAY()-3))>=3

=SUMPRODUCT((B2:B15=$F4)*(C2:C15=$G4)*(A2:A15>=TODAY()-4))>=4

and the formula in cell H4 is ... =IF(COUNTIF(I4:L4,TRUE)=4,"overworked","not_overworked")

I hope this helps.

 

[gg]

I need to figure our how to post my excel layout...
Let me try to figure it out..

I think you guy's have the idea but we are a little different.

 

[gg]

Ok folks lets start over.....
Below is what I want to happen. Columns A,B & C are input on a daily basis.
However, I want to see the impact of several test over a short period. Through out the year there will be 100 total test and the schedule dynamics have an impact. I am just not sure what it is...

I am not sure what is the easiest method. I thought I could get a SUM(If statement to work but I can't...

Column D,E & F should be formulas looking back to see what criteria is being met.

Please let me know what you think.

Sorry this has taken so long...Learning the htm maker was another learning experience.

Book1.xls
	A	B	C	D	E	F
1	Date	Name	Score	backtoback(BTB)	3test4days(3N4)	4test5days(4N5)
2	1/1/2004	Pete	75	BTB
3	1/1/2004	Cindy	80	BTB	BTB
4	1/2/2004	Cindy	82
5	1/3/2004	Bob	89
6	1/3/2004	Pete	95
7	1/4/2004	Al	96
8	1/4/2004	Bob	91	BTB
9	1/4/2004	Cindy	93
10	1/4/2004	Pete	88	BTB	3N4
11	1/5/2004	Cindy	72	BTB	3N4
12	1/6/2004	Al	76
13	1/6/2004	Bob	49
14	1/6/2004	Pete	89
15	1/7/2004	Al	96	BTB	3N4
16	1/7/2004	Bob	65	BTB	3N4	4N5
17	1/7/2004	Cindy	87
18	1/7/2004	Pete	92	BTB	3N4	4N5
Sheet1

 

[Yogi Anand]

Hi gg:

I don't think I follow the results you have posted in columns D, E, and F in your last post. However, please look at ...

y040106h1.xls
	A	B	C	D	E	F	G	H	I
1	Date	Name	Score	back	3N4	4N5	intermediarycolumns
2	01/01/2004	Pete	75	BTB	3N4	4N5	2	3	4
3	01/01/2004	Cindy	80				1	3	3
4	01/02/2004	Cindy	82				1	3	3
5	01/03/2004	Bob	89	BTB	3N4	4N5	2	3	4
6	01/03/2004	Pete	95	BTB	3N4	4N5	2	3	4
7	01/04/2004	Al	96	BTB	3N4		2	3	3
8	01/04/2004	Bob	91	BTB	3N4	4N5	2	3	4
9	01/04/2004	Cindy	93				1	3	3
10	01/04/2004	Pete	88	BTB	3N4	4N5	2	3	4
11	01/05/2004	Cindy	72				1	3	3
12	01/06/2004	Al	76	BTB	3N4		2	3	3
13	01/06/2004	Bob	49	BTB	3N4	4N5	2	3	4
14	01/06/2004	Pete	89	BTB	3N4	4N5	2	3	4
Sheet5b (2)

For sake of simplicity, and to help debug during development, I have use columns G, H, and I to hold intermediary calculations.

Please review the results and post back if these are OK -- otherwise explain a little further and then let us take it from there.