Formula difficulty - sumproduct I believe.

Zakkala

Active Member
Joined
Nov 12, 2004
Messages
254
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
There are indeed column headers - I'm off to investigate DCOUNT as it's not one I'm familiar with; thanks fellas!
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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?

Disable AdBlock

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
Back
Top