StudentRed
New Member
 Joined
 Dec 21, 2020
 Messages
 7
 Office Version

 365
 2016
 Platform

 Windows
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 (nonworking) 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 nonworking) 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.
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 (nonworking) 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 nonworking) 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.