# Help Creating a Dynamic Countifs Formula

#### randiliana

##### New Member
What I am trying to do, is to count all the cows, bred to a certain bull that are still in the herd, and that are going to calve on the first cycle (easy stuff). Problem is that the 1st cycle dates vary a bit from bull to bull.

So I want it to pull the date off of a 2nd sheet (Bulls) and compare it to the due dates on the first sheet (Breeding) and then return the answer on another sheet(Breeding Anal)... what I've got is

=COUNTIFS(Breeding!\$D:\$D,B\$1,Breeding!\$K:\$K,"<>Y",Breeding!\$R:\$R,">0",Breeding!\$R:\$R,"<="&Bulls!G2)

But I want to make it dynamic, so I don't literally have to go and change "<="&Bulls!G2 for each bull (there's 14 columns)
I've tried

=COUNTIFS(Breeding!\$D:\$D,B\$1,Breeding!\$K:\$K,"<>Y",Breeding!\$R:\$R,"<="Index(Bulls!\$G:\$G,Match(B\$1,Bulls!\$A:\$A)

but that doesn't work... any ideas?

The Breeding Sheet

The Bulls Sheet

The Analysis Sheet

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### AlanY

##### Well-known Member

"<="&VLOOKUP(Bulls!G\$2,Bulls!\$A:\$G,7,0)

#### AlanY

##### Well-known Member
actually your original formula should work with minor amendment

"<="&INDEX(Bulls!\$G:\$G,MATCH(B\$1,Bulls!\$A:\$A,0))

#### AliGW

##### Banned
"The Bulls Sheet" -

#### AlanY

##### Well-known Member
and the

What I am trying to do, is to count all the cows, bred to a certain bull that are still in the herd, and that are going to calve on the first cycle (easy stuff). Problem is that the 1st cycle dates vary a bit from bull to bull.

So I want it to pull the date off of a 2nd sheet (Bulls) and compare it to the due dates on the first sheet (Breeding) and then return the answer on another sheet(Breeding Anal)... what I've got is

#### randiliana

##### New Member

"<="&VLOOKUP(Bulls!G\$2,Bulls!\$A:\$G,7,0)

Thank you, I figured it was probably something simple. Works exactly as I had hoped.

#### randiliana

##### New Member

"<="&VLOOKUP(Bulls!G\$2,Bulls!\$A:\$G,7,0)

Thank you, but for some reason this one does not work, and I am not coherent enough with using VLOOKUP to trouble shoot it.

#### randiliana

##### New Member
OK, now what am I doing wrong? Seems like it should be straight forward.... I want it to count the # between 2 dates

=COUNTIFS(Breeding!\$D:\$D,C\$1,Breeding!\$K:\$K,"<>Y",Breeding!\$R:\$R,"<="&INDEX(Bulls!\$H:\$H,MATCH(C\$1,Bulls!\$A:\$A,0),Breeding!\$R:\$R,">"&INDEX(Bulls!\$G:\$G,MATCH(C\$1,Bulls!\$A:\$A,0))))

#### AlanY

##### Well-known Member
nothing stick out. what do you get from

=INDEX(Bulls!\$H:\$H,MATCH(C\$1,Bulls!\$A:\$A,0) ?

#### randiliana

##### New Member
It gives me the proper answer.

Replies
0
Views
695
Replies
1
Views
541
Replies
5
Views
317
Replies
14
Views
681
Replies
2
Views
1K

1,190,798
Messages
5,982,981
Members
439,810
Latest member
phobo3s

### 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.

### Which adblocker are you using?

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

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