Divide by count if Specific text.

[RyanH002]

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

 

[StephenCrump]

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:

	A	B	C	D	E	F	G	H	I	J
1
2		MyLookup
3		2	95%					Yours	Mine	Adjusted?
4		3	90%			300	1	1,300	1,300	1,300
5		4	85%			875	20	525	525	525
6		5	80%			2,000	MC	-100	-100	-75
7		6	70%			12,500	75	0	0	0
8		11	60%			550	3	495	495	495
9		21	40%			1,750	MC	-100	-100	-75
10		31	20%			1,875	MC	-100	-100	-75
11		41	10%
12		51	0%			475	MC	-100	-100	-75
Sheet1

Cell Formulas
Range	Formula
H4:H10,H12	H4	=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,I12	I4	=IF(G4=1,F4+1000,IF(G4="MC",-100,IF(G4="WD",0,F4*VLOOKUP(G4,MyLookup,2,1))))
J4:J10,J12	J4	=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
Name	Refers To	Cells
MyLookup	=Sheet1!$B$3:$C$12	I12:J12, I4:J10

 

[RyanH002]

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
	A	B	C	D	E	F	G	H	I
4	Position		Player	Bet Amount	Odds Value	Points value For Win	Player finishing Position	Points Awarded	Total Points
5	1	Simon	Spieth	25	12	300	1	1300	1525
6			Tringale	25	35	875	20	525
7			Dahmen	25	80	2000	MC	-300
8			Lebioda	25	500	12500	75	0
9	2	Adam	Conners	25	22	550	3	495	-105
10			Higgs	25	70	1750	MC	-300
11			Howell	25	75	1875	MC	-300
12			Landry					FALSE
13	3	Ryan	Morikawa	25	19	475	MC	-300	-300
14			Woodland					FALSE
15			Wallace					FALSE
16			Cabrera Bello					FALSE
PGALife Boys

Cell Formulas
Range	Formula
H5:H16	H5	=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,I9	I5	=SUM(H5:H8)
F13,F5:F11	F5	=E5*D5
A5,A13,A9	A5	=RANK(I5,$I$5:$I$16)

Cells with Data Validation
Cell	Allow	Criteria
C5:C13	List	='Golfers List'!$A$2:$A$319
C14:C16	List	='Golfers List'!$A$2:$A$500