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.

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Well-known Member
You should use the ranges with same rows. for example \$E\$1:\$E\$10000 with \$K\$2:\$K\$10000 or \$E\$1:\$E\$8000 with \$K\$2:\$K\$8000 and Seperate C4 & C5
Try this:
Excel Formula:
``=SUMPRODUCT(--(INDIRECT("'"&\$C\$4&"'!\$E\$1:\$E\$10000")>=3),--(INDIRECT("'"&\$C\$5&"'!\$E\$1:\$E\$10000")>=3),COUNTIF(INDIRECT("'"&\$C4&"'!\$K\$2:\$K\$10000"),INDIRECT("'"&\$C5&"'!\$K\$2:\$K\$10000")))``

StudentRed

New Member
You should use the ranges with same rows. for example \$E\$1:\$E\$10000 with \$K\$2:\$K\$10000 or \$E\$1:\$E\$8000 with \$K\$2:\$K\$8000 and Seperate C4 & C5
Try this:
Excel Formula:
``=SUMPRODUCT(--(INDIRECT("'"&\$C\$4&"'!\$E\$1:\$E\$10000")>=3),--(INDIRECT("'"&\$C\$5&"'!\$E\$1:\$E\$10000")>=3),COUNTIF(INDIRECT("'"&\$C4&"'!\$K\$2:\$K\$10000"),INDIRECT("'"&\$C5&"'!\$K\$2:\$K\$10000")))``

It seems like this too returns #VALUE!

StudentRed

New Member
Update:

It does not return #value! (the formula was still working and ad not updated)

Well-known Member

Are you filled Cell C4 & C5.

StudentRed

New Member

Here is a screen shot of a datasubset using your formula. In this subset the INDIRECT sheet names are in column B

Well-known Member
I misstyped formula. your criteria row numbers doesn't equal to your countif range.
Do one of these items:
1. Change criteria to E2:E10000
OR
2. change countif range to K1:K10000
OR
3.Change Countif range to K2:K10001

And for these situation it is better upload file with XL2BB addin at the above of reply section.

StudentRed

New Member
I misstyped formula. your criteria row numbers doesn't equal to your countif range.
Do one of these items:
1. Change criteria to E2:E10000
OR
2. change countif range to K1:K10000
OR
3.Change Countif range to K2:K10001

And for these situation it is better upload file with XL2BB addin at the above of reply section.

StudentRed

New Member
Hi maabai,

I changed the formula as per what you said and interestingly it presents an answer...but I am not sure how it arrived at that answer. Also, that is not the same answer I get when I do it manually. I must have something else wrong?

The answer I arrive at manually is 520 for cell D8. The answer your formula gives me is 312.

I enter your formula as follows:
Excel Formula:
``=SUMPRODUCT(--(INDIRECT("'"&\$B4&"'!\$E\$2:\$E\$10000")>=3),--(INDIRECT("'"&\$B5&"'!\$E\$2:\$E\$10000")>=3),COUNTIF(INDIRECT("'"&\$B4&"'!\$K\$2:\$K\$10000"),INDIRECT("'"&\$B5&"'!\$K\$2:\$K\$10000")))``

Here is a subset of the workbook (for which the value in d8 should be 520):
12.21.2020_DEG_subset2.xlsx

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

1,127,998
Messages
5,628,043
Members
416,289
Latest member
Jbelisari

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.

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