Awkward formula - referencing 2 columns

[Briscoefilms]

Hi guys, this ones got me stumped. I've been through some old posts referencing similar, but im still having no luck.

So I'm trying to calculate the number of times "Dec" is entered in a column, whilst also have "No" listed in another column. However, the ranges differ considerably from each other.

'Referral Data'!$W$127:$W$999,"Dec" being the range I need to draw the amount of Dec from.
'Referral Data'!$X$671:$X$731,"No" being the range I need to cross reference for No results.

So I want to achieve the amount of names Dec and No both apply against. I will also have to do the same with Yes, but hopefully sorting this one out, will mean that will too.

 

[AliGW]

Why can't you make the ranges the same size? If you do, you can use COUNTIFS.

=COUNTIFS('Referral Data'!$W$127:$W$999,"Dec",'Referral Data'!$X$127:$X$999,"No")

 

[Veritan]

I think you'll need to provide more information, particularly about the cross-referencing you are referring to. Have you tried the COUNTIF and/or COUNTIFS functions? It's not really a big deal that your ranges start at different rows, but since they are different sizes, that will definitely have an impact. Can you give an example of what your data looks like and what a correct result would be?

 

[Briscoefilms]

oh god, I'm blaming Xmas. I was thinking I can't because the No column will then pick up No from previous months.....but of course its filtering the month. Thank Ali, complete and utter brain fart. Thanks.

 

[AliGW]

You're welcome!