Formula problem

[rightdp]

I work for a travel-nursing agency. We send nurses to hospitals aroun
the country on a contractual basis. I am trying to create
spreadsheet that will monitor how many contracts are "closed" (al
paperwork is completed) each month. I am using a query to pull all o
the information from our database. All but one thing is working great
Our quota is 4-13 week contracts per month. I am using the followin
formula to monitor total number of 13-week contracts closed by eac
Recruiter:

{=SUM(IF(G7:G1000>=13,IF(H7:H1000="JaneD",1,0)))}

Column G is where the number of contracted weeks is shown. JaneD i
the name of the Recruiter. This formula works great because it count
every contract that is greater than or equal to 13 as one contrac
toward the quota. The problem is, if the contract is 26 weeks, thi
will count as two contracts toward the quota but my formula stil
counts it one time. I basically need to know if it is possible t
create a formula that will count as one toward the quota for every 1
weeks. i.e.- 13 weeks = 1, 24 weeks = 1, 26 weeks = 2

I hope this doesn’t sound ridiculously confusing. Thanks for an
help!

-Dusti

 

[David McRitchie]

Check for >=26 use 2, else failure the rest of you formula
and another close paren..

Untested but this should do it.
{=SUM(IF(G7:G1000>=26,2,IF(G7:G1000>=13,IF(H7:H1000="JaneD",1,0))))}
--

 

[rightdp]

thanks for the quick response. I got excited there for a minute.
thought there was no way it could be that easy. Turns out it isn'
that easy ..... it didn't work

I'm still open for more suggestions

 

[rightdp]

Mr. McRitchie, I apologize. Actually it did work! It caused a
different problem somewhere else but I think I can fix it. I certainly
appreciate your help!