Beginner

[Horizon]

Following is the data in the range A1:B9

Name Score
-----------------
Tom 10
Tom 40
**** 39
**** 0
**** 15
Harry 7
Pandu 45

Range D1:E4 contains the following.

Tom _____
**** _____
Harry _____
Pandu _____

Now I need a formula in the range E1:E4 based on a criteria. The criteria is, in the cell E1, it will check the scores of Tom from the above range and check if he has scored below 30, if yes, the count will be 1 else 0. If he has scored less than 30 on more than one occassions, the count will accordingly change from 1 to 2 or 3

Horizon

 

[Greg Truby]

{Edit}Forgot my manners. Welcome to the board!{EndEdit}

Book2
	A	B	C	D	E
1	Tom	10		Tom	1
2	Tom	40		****	2
3	****	39		Harry	1
4	****	0		Pandu	0
5	****	15
6	Harry	7
7	Pandu	45
Sheet3

Formula in E1 is =SUMPRODUCT(($A$1:$A$7=D1)*($B$1:$B$7<30))

 

[just_jon]

Following is the data in the range A1:B9

Name Score
-----------------
Tom 10
Tom 40
**** 39
**** 0
**** 15
Harry 7
Pandu 45

Range D1:E4 contains the following.

Tom _____
**** _____
Harry _____
Pandu _____

Now I need a formula in the range E1:E4 based on a criteria. The criteria is, in the cell E1, it will check the scores of Tom from the above range and check if he has scored below 30, if yes, the count will be 1 else 0. If he has scored less than 30 on more than one occassions, the count will accordingly change from 1 to 2 or 3

Horizon

Welcome to the Board -- can we interest you in something in the SUMPRODUCT line?

=SUMPRODUCT(--($A$1:$A$9=D1),--($B$1:$B$9<30))

in E1, copied to E4.

 

[Zack Barresse]

you mean like this??...

Book4
	A	B	C	D	E
1	Tom	10		Tom	1
2	Tom	40		****	2
3	****	39		Harry	1
4	****	0		Pandu	0
5	****	15
6	Harry	7
7	Pandu	45
Sheet1

 

[phantom1975]

Is this what you are looking for:

Book1
	A	B	C	D	E
1	Tom	10		Tom	1
2	Tom	40		****	2
3	****	39		Harry	1
4	****	0		Pandu	0
5	****	15
6	Harry	7
7	Pandu	45
Sheet1

 

[Horizon]

That was too quick Greg.

Thanx

 

[just_jon]

Altogether now! ...latest poll results, * 2, -- 1... film at 11...

 

[Zack Barresse]

hey! welcome to the board!

sumproduct answers all!!

 

[Greg Truby]

Hi Jon! Thanks. I had tried it as comma separated first and fell on my face, so switched to multiplying 'em. Now I see that I'd forgotten the ruddy coercer. Someday I'll get the hang of SUMPRODUCT...

 

[phantom1975]

Altogether now! ...latest poll results, * 2, -- 1... film at 11...

This would be a GREAT one for Aladin to get his hands on. I thought I had learned a lesson from way back when, that * is the faster method.

Alright, I'll stop now. I'll leave the Vulcan logic crap to Aladin. He knows what he's doing anyways....