COUNTIF Problem

Hi Everyone,

Could you let me know how to rework this formula to better serve me ...

I am currently using this:
=LOOKUP(COUNTIF(J5:FU5,"Completed"),{0,19,48,71,85,95,101,111,130,150,168},{"Starting Grade (£6.31)","Grade 1 (£6.45)","Grade 2 (£6.55)","Grade 3 (£6.75)","Grade 4.1 (£6.95)","Grade 4.2 (£7.25)","Grade 4.3 (£7.45)","Grade 4.4 (£7.55)","Grade 5 (£7.95)","Grade 6 (£8.45)","Grade 7 (£8.95)"})

So if 0-18 occurrences of "Completed" are found within the range then "Starting Grade (£6.31)" is displayed in the cell. If 19-47 occurrences are found then "Grade 1 (£6.45)" is displayed and so on.

I want to update this and change the "Completed" to a choice of multiple words (i.e. January, February, March, April ... December) so if any of these words are found they count as 1 value. So any month would count the same as one "Completed" in the old formula.

Is this possible?

Hello
Everything in your formula appears to be Hard-Coded, which makes change difficult. You need to use lookup tables and cell references to make it truly dynamic.

DBY

try replacing if(or(J5:FU5={"january","february","march","April","may","june","july","august","september","october",november","December"}

I m not sure as I don't have the file...see if this helps u...

Example File.xlsx

I have attached an example file. Now the drop down box gives the option that I want as opposed to the original "Completed". If someone could think of a way to help that would be great.

Thanks so much,

Ewan

Hello Ewan
I'll have a look at your file shortly but I have to leave the computer for an hour or so. Perhaps in the meantime someone else will assist.

DBY

Thanks so much DBY I really appreciate it!

Try changing this part
COUNTIF(J5:FU5,"Completed")

to
SUMPRODUCT(COUNTIF(J5:FU5,TEXT(DATE(1,ROW($1:$12),1),"mmmm")))

As I mentioned initially, I was thinking of Lookup Tables for your grades and criteria lists, as these can easily be changed or amended with new values. Take a look at the attached amended version of your file. Are these the values you're trying to return?

DBY