Countif problem

Craig1

Active Member
Joined
Jun 11, 2009
Messages
322
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
What is in the cells between the ones you are looking at? And what is J2?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Try...

=SUMPRODUCT(--(MOD(ROW(January!$B$7:$B$56)-ROW(January!$B$7),5)=0),--(January!$B$7:$B$56=Sheet2!$J2))
 
Upvote 0
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.
 
Upvote 0
Thanks Aladin,
I rearranged the rows to every 7th and it worked great.

Thanks again and thanks to Dreid1011.

Craig.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top