# SUMPRODUCT(INDIRECT(criteria),COUNTIF(INDIRECT(match query1),INDIRECT(match query 2)

#### StudentRed

##### New Member
Hi all,

I am trying to query 2 lists for matching text values that are unique on each sheet (but are duplicated on other sheets). However, I only want to query those that match given criteria.

Sheet names are listed in column C for the INDIRECT function on the sheet where I want to create a summary table. I want to look up values in Sheet2 column K that match values in Sheet3 column K, for cases when both Sheets2&3 column E is >=3.

I have made several attempts at this and I can narrow it down to the first INDIRECT (highlighted in red) criteria function that is troublesome (everything after COUNTIF works fine)

Here is my (non-working) formula:

=SUMPRODUCT(--(INDIRECT("'"&\$C\$4:\$C\$5&"'!e1:e10000")>=3),COUNTIF(INDIRECT("'"&\$C4&"'!\$k\$2:\$k\$10000"),INDIRECT("'"&\$C5&"'!\$k\$2:\$k\$10000")))

This returns #VALUE!

Alternative (equally non-working) formula:

=SUMPRODUCT(IF(AND(INDIRECT("'"&\$C4&"'!\$e\$1:\$e\$9000")>=3,INDIRECT("'"&\$C5&"'!\$e\$1:\$e\$9000")>=3),COUNTIF(INDIRECT("'"&\$C4&"'!\$k\$2:\$k\$8000"),INDIRECT("'"&\$C5&"'!\$k\$2:\$k\$8000")),0))

This returns 0

These are both incorrect formulas as determined by filtering both sheets for the column E >=3 then copy and paste column K from both sheets into a new sheet and conditionally format for duplicate values, duplicates are found. Note that column K values are unique and not duplicated within a sheet but can be found on other sheets. Thus, something in the formulas is not correct.

##### Well-known Member
How to calculate than this? I see your file. what you want exactly calculate?
For Example,This formula First check, Test2!E2>=3 & Test3!E2>=3 Then if both of them Correct, calculate Countif Formula.
I check all of Values >=3 at Test2 Sheet is 1206 & at Test3 Sheet is 1108 AND for Both of them is 584
AND Your Countif formula also calculate Empty Cells if at both sheet is empty.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Replies
6
Views
471
Replies
4
Views
360
Replies
10
Views
335
Replies
1
Views
179
Replies
2
Views
157

1,128,055
Messages
5,628,346
Members
416,311
Latest member
S991102

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