Complex Formula Help

[easybpw]

Hello,

Thank you in advance for your help. I have a complex formula issue, at least for me it is. I need a formula that has the following conditions:

I need to count the number of times a word shows in column D but only if column E says "NEW" or "RET". Then I need to take that result and if falls within a range (1-3, 4-5, 6-7, 8-9, and 10 and above) take that and have it relate to a dollar amount.

So for example if the result from column d is 4 or 5 then the dollar amount would be $7,500. If it falls within 6 - 7 then the dollars would be $8,000 and so on. How can this be accomplished with a formula. I prefer to not add columns if I don't have to. I've tried different things but I'm stumped.

Thanks again!

Bill

 

[Marcelo Branco]

Try to provide a small data sample along with expected result.

M.

 

[jtakw]

Hi,

May be something like this.
As you didn't provide the full details, the Criterion, Lookup Vector, and Result Vector may need adjusting:

Book1
	D	E	F	G
2	Word	New		8500
3	Word	Ret
4	Word	Old
5	Word	Ret
6	Word	Other
7	Word	Ret
8		New
9	Word	Old
10	Word	New
11	Word	New
12		Ret
13	Word	Ret
14	Word	New
15	Word	Old
Sheet89

Cell Formulas
Range	Formula
G2	=LOOKUP(SUM(COUNTIFS(D2:D15,"Word",E2:E15,{"New","Ret"})),{0,4,6,8,10},{0,7500,8000,8500,9000})

 

[easybpw]

That looks awesome. I'll try it out and let you know if it doesn't work. Thanks! I wouldn't have figured that out myself. Much appreciated!

 

[Joyner]

Hello, are you looking to do something like this:

=IFERROR(LOOKUP(COUNTIFS($D$1:$D$11,"word",$E$1:$E$11,"new")+COUNTIFS($D$1:$D$11,"word",$E$1:$E$11,"ret"),{0,4,6,8,10},{5000,6000,7500,8000,10000}),0)

Correct Lookup vector per above

 

[Joyner]

Hello, are you looking to do something like this:

=IFERROR(LOOKUP(COUNTIFS($D$1:$D$11,"word",$E$1:$E$11,"new")+COUNTIFS($D$1:$D$11,"word",$E$1:$E$11,"ret"),{0,4,6,8,10},{5000,6000,7500,8000,10000}),0)

Correct Lookup vector per above

Did not correct the results vector to match or should the lookup vector be "{1,4,6,8,10}?