# Formula difficulty - sumproduct I believe.

#### Zakkala

##### Active Member
Hi there,

I'm having problems getting a forumla to do the following - any ideas gratefully received.

The formula will be in Worksheet 1 and I am trying to count the number of rows on a Worksheet 2 where data in four columns matches, or contains, the data in four columns on a row in a Worksheet 3. In the following scenario, the result for the formula would be 2 as two rows on Worksheet 2 either match or contain data from rows on Worksheet 3. The only column that will have to 'contain' rather than match is Column D on Worskheet 2. There would be several thousand rows of data on each sheet.

Worksheet 2

Column A Column D Column E Column F
Billing 228359202 6 SMB
Access 123456701 6 SMB
Access 228359202 6 SMB

Worksheet 3

Column A Column F Column L Column P
Access 1234567 SMB 6
Access 2283592 SMB 6

I hope that makes sense, but please just let me know if you need any further info.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Mr. Walnuts

##### Board Regular
does it need to match ALL FOUR columns? Or just a number of columns?

This sounds like you could use a COUNTIFS function.

Hmm... Maybe a DCOUNTA, but it sounds like more of a match problem

Last edited:

#### mikerickson

##### MrExcel MVP
Are there headers on the data in sheets 2 and 3?
If so, do they match with each other?

If so, DCOUNTA could be used, with the range in sheet2 being the dataRange, the data in sheet3 the criteriaRange.

#### Zakkala

##### Active Member
For a row to count:

Worksheet 2 Column A needs to match Worksheet 3 Column A
Worksheet 2 Column D needs needs to contain the data in Worksheet 3 Column F
Worksheet 2 Column E needs to match Worksheet 3 Column L
Worksheet 2 Column F needs to match Worksheet 3 Column P

Hope that makes sense.

#### Mr. Walnuts

##### Board Regular
As long as the column headers are the same, it shouldn't matter much which Column "Letter" they are.

#### Zakkala

##### Active Member
There are indeed column headers - I'm off to investigate DCOUNT as it's not one I'm familiar with; thanks fellas!

Replies
8
Views
233
Replies
1
Views
151
Replies
1
Views
750
Replies
6
Views
278
Replies
1
Views
191

1,191,215
Messages
5,985,313
Members
439,957
Latest member
venky2002

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

### Which adblocker are you using?

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

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