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.
 
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.
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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