Countif problem

Craig1

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

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
What is in the cells between the ones you are looking at? And what is J2?
 

Craig1

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

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
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

Active Member
Joined
Jun 11, 2009
Messages
320

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Try...

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

Craig1

Active Member
Joined
Jun 11, 2009
Messages
320

ADVERTISEMENT

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.
 

Craig1

Active Member
Joined
Jun 11, 2009
Messages
320
Thanks Aladin,
I rearranged the rows to every 7th and it worked great.

Thanks again and thanks to Dreid1011.

Craig.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,738
Messages
5,597,829
Members
414,180
Latest member
Sir Khaya

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
Top