# Awkward formula - referencing 2 columns

#### Briscoefilms

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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### AliGW

##### Banned
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")

Last edited:

#### Veritan

##### Active Member
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

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

You're welcome!

Replies
1
Views
886
Replies
6
Views
671
Replies
1
Views
355
Replies
6
Views
483
Replies
2
Views
713

1,190,842
Messages
5,983,196
Members
439,828
Latest member
summertime65

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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