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

StudentRed

New Member
Joined
Dec 21, 2020
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. 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 (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.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
Members
414,387
Latest member
Vincent88

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
Top