Countif problem

[Craig1]

Hi Guys,
I have eventually worked out how to do a complicated (to me anyway) countif but i'm sure there must be an easier and more economical way to do it.
My work around is:-

=COUNTIF(January!$B$7,Sheet2!$J2)+COUNTIF(January!$B$12,Sheet2!$J2)+COUNTIF(January!$B$17,Sheet2!$J2)+COUNTIF(January!$B$22,Sheet2!$J2)+COUNTIF(January!$B$27,Sheet2!$J2)+COUNTIF(January!$B$34,Sheet2!$J2)+COUNTIF(January!$B$39,Sheet2!$J2)+COUNTIF(January!$B$44,Sheet2!$J2)+COUNTIF(January!$B$51,Sheet2!$J2)+COUNTIF(January!$B$55,Sheet2!$J2)+COUNTIF(January!$B$56,Sheet2!$J2)
As you can see it is a long way around, and then I had to do the same for every other month and other criteria besides J2.
I tried plenty of different ways I found on this and various other sites, all to no avail.
Is there a more efficient way to do this countif???

Thanks in advance.

Craig.

 

[dreid1011]

What is in the cells between the ones you are looking at? And what is J2?

 

[Craig1]

Thanks for the reply,
the cells in between hold other info which doesn't need counting, again it's names but in the cells counted are names of people who have carried out audits, in the cells are names of persons under audit and various other people, J2 holds the name I want to count.

Thanks again.

Craig.

 

[dreid1011]

Thanks for the reply,
the cells in between hold other info which doesn't need counting, again it's names but in the cells counted are names of people who have carried out audits, in the cells are names of persons under audit and various other people, J2 holds the name I want to count.

Thanks again.

Craig.

Then is it possible for the name in J2 to appear in the between cells, and what is the criteria to only look at the specific cells other than you manually choosing them? Is there another column with criteria we can use?

 

[Craig1]

The name in J2 will appear on numerous occasions in between the cells I want to count and the criteria is that these persons have carried out the correct amount of audits, but they could also be the auditee, hence I the cells in between, so in column A there is Auditor, so in A7, A12 etc they all have the word Auditor in there, so in the adjacent cell in column B is the name I want to count.

Thanks again.

Craig.

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--(MOD(ROW(January!$B$7:$B$56)-ROW(January!$B$7),5)=0),--(January!$B$7:$B$56=Sheet2!$J2))

 

[Craig1]

Thanks Aladin,
That nearly got it, the divisor of 5 is nearly correct but it happens in 3 sections, section 1 is B7 to B27 (5 audits for 1 type of job), section 2 is B34 to B44 (3 audits for another type of job) and section 3 is B51 to B61 (3 adits for another type of jobs) note error in original post with the last 2 sets of B numbers. So the rows between the sections go to 7 instead of the 5 you used.

Nearly there.
Thanks

Craig.

 

[Aladin Akyurek]

Can't you rearrange your layout as every 5th or as every 7th?

 

[Craig1]

Thanks Aladin,
I rearranged the rows to every 7th and it worked great.

Thanks again and thanks to Dreid1011.

Craig.

 

[Aladin Akyurek]

Craig,

That's great. Thanks for the update.