Beginner

Horizon

New Member
Joined
Mar 3, 2004
Messages
2
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
Joined
Jun 19, 2002
Messages
10,022
{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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
you mean like this??...
Book4
ABCDE
1Tom10Tom1
2Tom40****2
3****39Harry1
4****0Pandu0
5****15
6Harry7
7Pandu45
Sheet1
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962

ADVERTISEMENT

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

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
hey! welcome to the board!

sumproduct answers all!!
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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
Joined
Jun 3, 2002
Messages
3,962
just_jon said:
Altogether now! :biggrin: ...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....
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top