Using COUNTIF(s) function with two dynamic named ranges set equal to each other as criteria

[mcavallod]

Hey Everyone,

This is my first post on the forum and I really need some help! Currently I have an IF function like this: IF(b4=f4,1,0) then a countif(results,"=1") where results is a dynamic named range of the results of the if function. I have to drag down the if formula for the entire column of data (b5=f5,b6=f6,etc.), forming a mess of ones and zeros in my sheet. I was wondering if there is a way to do all of this in one step. I tried to do many different commands (IF and COUNTIF) involving using a dynamic named range of all of the b column and a dynamic named range of the f column set equal to each other in the criteria part of the functions, but this just won't work. Does anyone have any insights into a way I can do this in one step? Thank you so much!

Mike

 

[Scott Huish]

Does this work for you?

=SUMPRODUCT(--(B4:B100=F4:F100),--(B4:B100<>""))

 

[mcavallod]

Scott,

Thanks much for your help and prompt response! The formula you provided works beautifully. Would the formula work if I provided dynamic named ranges for the columns instead of the explicit ranges like shown above? I tried to put in my named ranges and I came up with an N/A error. Could this be because I defined my ranges incorrectly? Thanks again you've been very helpful!

Mike

 

[Scott Huish]

Are both named ranges the exact same size?

 

[mcavallod]

Yes they are. However, I found that the way you defined the range is sufficient for my purposes, so I will continue to use the function that way. Thanks again for all your help!