# Beginner

#### Horizon

##### New Member
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Greg Truby

##### MrExcel MVP
{Edit}Forgot my manners. Welcome to the board!{EndEdit}
Book2
ABCDE
1Tom10Tom1
2Tom40****2
3****39Harry1
4****0Pandu0
5****15
6Harry7
7Pandu45
Sheet3

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

#### just_jon

##### Legend
Horizon said:
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

##### MrExcel MVP
you mean like this??...
Book4
ABCDE
1Tom10Tom1
2Tom40****2
3****39Harry1
4****0Pandu0
5****15
6Harry7
7Pandu45
Sheet1

#### phantom1975

##### MrExcel MVP

Is this what you are looking for:
Book1
ABCDE
1Tom10Tom1
2Tom40****2
3****39Harry1
4****0Pandu0
5****15
6Harry7
7Pandu45
Sheet1

#### Horizon

##### New Member
That was too quick Greg.

Thanx

#### just_jon

##### Legend

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

#### Zack Barresse

##### MrExcel MVP
hey! welcome to the board!

#### Greg Truby

##### MrExcel MVP
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

##### MrExcel MVP
just_jon said:
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....

Replies
4
Views
190
Replies
13
Views
338
Replies
7
Views
363
Replies
1
Views
110
Replies
3
Views
336

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,885
Messages
5,766,936
Members
425,388
Latest member
Cave_Johnson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back