Countifs with offset returning #value

[Simba75]

Hoping somebody can help out...please

I have a sheet that I want to do countifs on but when I put 2 working countifs together in one formula I get #value?

1. =COUNTIFS(Colleagues!$E:$E,$B$4) - returns the correct result
2. =COUNTIFS(OFFSET(Colleagues!$A$3,1,MATCH($B$2-1,Colleagues!$3:$3,0),20000,1),"*"&$C$2&"*") - returns the correct result

Join them together to get the result I want:
3. =COUNTIFS(Colleagues!$E:$E,$B$4,OFFSET(Colleagues!$A$3,1,MATCH($B$2-1,Colleagues!$3:$3,0),20000,1),"*"&$C$2&"*") - just get #value as result?

Can anybody suggest where I am going wrong as this is melting my brain.

Thanks in advance for advice

 

[jasonb75]

Your count ranges are not the same size. With both the same it should work, if not then that would mean no match found.

=COUNTIFS(Colleagues!$E$4:$E$20003,$B$4,OFFSET(Colleagues!$A$3,1,MATCH($B$2-1,Colleagues!$3:$3,0),20000,1),"*"&$C$2&"*")

 

[Simba75]

Jason, you are a star! Thank you so much. This formula works:-
=COUNTIFS(OFFSET(Colleagues!$A$3,1,4,20000,1),$B$4,OFFSET(Colleagues!$A$3,1,MATCH($B$2-1,Colleagues!$3:$3,0),20000,1),"*"&$C$2&"*")

For my education, it seems a bit unnecessary calculation-wise for the first offset when I know the range, is there a way to get around this in a more CPU resource way?

Thanks again for your guidance, its much appreciated!

 

[jasonb75]

it seems a bit unnecessary calculation-wise for the first offset when I know the range, is there a way to get around this in a more CPU resource way?

How about the way that I re-wrote your formula in my first reply?

Which version of excel are you using? With an up to date version there are better ways to do the same.

 

[Simba75]

Oh yeah, I thought you had just snipped the formula that I had posted and by some black magic it was returning a range.... but I now see that you worked it out and amended it.

Your re-write suggestion also works perfectly. Kudos to you, you have solved in minutes what has been puzzling me for hours.

Thank you!