Help Creating a Dynamic Countifs Formula

[randiliana]

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

 

[AlanY]

try this instead

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

 

[AlanY]

actually your original formula should work with minor amendment

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

 

[AliGW]

"The Bulls Sheet" -

 

[AlanY]

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]

try this instead

"<="&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]

try this instead

"<="&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]

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]

nothing stick out. what do you get from

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

 

[randiliana]

It gives me the proper answer.