# Divide by count if Specific text.

#### RyanH002

##### New Member
Hi,

I have quite a specific and in my mind complex, not only to write but explain.

I have a formula I want that counts the cells that have any information (I believe this to be a wildcard and uses "*") then use that number to multiply if that range of cells has an MC. But this needs to be relative across the 4 cells.

The premise is,
1 cell with content, MC = -300
2 cells, each MC = -150
3 cells, each MC = -100
4 cells, each MC = -75

The Points Awarded collumn already has this formula, which alson needs to be included:

=IF(G5=1,F5+1000,IF(AND(G5=2),F5*95%,IF(AND(G5=3),F5*90%,IF(AND(G5=4),F5*85%,IF(AND(G5=5),F5*80%,IF(AND(G5>5,G5<11),F5*70%,IF(AND(G5>10,G5<21),F5*60%,IF(AND(G5>20,G5<31),F5*40%,IF(AND(G5>30,G5<41),F5*20%,IF(AND(G5>40,G5<51),F5*10%,IF(AND(G5>50,G5<100),0,IF(G5="MC",-100,IF(G5="WD",0)))))))))))))

Thanks for any help

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

I have used a VLOOKUP to simplify your formula. I'm not clear what you mean by relative across the 4 cells, but am guessing:

ABCDEFGHIJ
1
2MyLookup
4390%30011,3001,3001,300
5485%87520525525525
6580%2,000MC-100-100-75
7670%12,50075000
81160%5503495495495
92140%1,750MC-100-100-75
103120%1,875MC-100-100-75
114110%
12510%475MC-100-100-75
Sheet1
Cell Formulas
RangeFormula
H4:H10,H12H4=IF(G4=1,F4+1000,IF(AND(G4=2),F4*95%,IF(AND(G4=3),F4*90%,IF(AND(G4=4),F4*85%,IF(AND(G4=5),F4*80%,IF(AND(G4>5,G4<11),F4*70%,IF(AND(G4>10,G4<21),F4*60%,IF(AND(G4>20,G4<31),F4*40%,IF(AND(G4>30,G4<41),F4*20%,IF(AND(G4>40,G4<51),F4*10%,IF(AND(G4>50,G4<100),0,IF(G4="MC",-100,IF(G4="WD",0)))))))))))))
I4:I10,I12I4=IF(G4=1,F4+1000,IF(G4="MC",-100,IF(G4="WD",0,F4*VLOOKUP(G4,MyLookup,2,1))))
J4:J10,J12J4=IF(G4=1,F4+1000,IF(G4="MC",-300/COUNTIF(G\$4:G\$12,"MC"),IF(G4="WD",0,F4*VLOOKUP(G4,MyLookup,2,1))))
Named Ranges
NameRefers ToCells
MyLookup=Sheet1!\$B\$3:\$C\$12I12:J12, I4:J10

#### RyanH002

##### New Member
Hi Stephen,

Thank you for getting back to me so quickly.

I've managed to work this out with your help. I was trying to do the count of cells and then the division, but you showed me to do the look up of MC then countif...

=IF(G8="MC",-300/COUNTIF(\$G\$5:\$G\$8,"<>"))

I'm not sure i understand the MyLookup bit on how to simplify the formula, but I'll work on that too. Thanks again.

Fantasy Golf V3.xlsm
ABCDEFGHI
4PositionPlayerBet AmountOdds ValuePoints value For WinPlayer finishing PositionPoints AwardedTotal Points
51SimonSpieth2512300113001525
6Tringale253587520525
7Dahmen25802000MC-300
8Lebioda2550012500750
10Higgs25701750MC-300
11Howell25751875MC-300
12LandryFALSE
133RyanMorikawa2519475MC-300-300
14WoodlandFALSE
15WallaceFALSE
16Cabrera BelloFALSE
PGALife Boys
Cell Formulas
RangeFormula
H5:H16H5=IF(G5=1,F5+1000,IF(AND(G5=2),F5*95%,IF(AND(G5=3),F5*90%,IF(AND(G5=4),F5*85%,IF(AND(G5=5),F5*80%,IF(AND(G5>5,G5<11),F5*70%,IF(AND(G5>10,G5<21),F5*60%,IF(AND(G5>20,G5<31),F5*40%,IF(AND(G5>30,G5<41),F5*20%,IF(AND(G5>40,G5<51),F5*10%,IF(AND(G5>50,G5<100),0,IF(G5="MC",-300,IF(G5="WD",0)))))))))))))
I5,I13,I9I5=SUM(H5:H8)
F13,F5:F11F5=E5*D5
A5,A13,A9A5=RANK(I5,\$I\$5:\$I\$16)
Cells with Data Validation
CellAllowCriteria
C5:C13List='Golfers List'!\$A\$2:\$A\$319
C14:C16List='Golfers List'!\$A\$2:\$A\$500

Replies
25
Views
371
Replies
2
Views
112
Replies
21
Views
275
Replies
3
Views
187
Replies
20
Views
826

1,129,568
Messages
5,637,093
Members
416,957
Latest member
Brovashift

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