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

[StudentRed]

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]

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:

=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]

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:

=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")))

Hi maabadi,

It seems like this too returns #VALUE!

 

[StudentRed]

Update:

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

Instead, the formula returns "0" when the answer should be "520".

 

[maabadi]

Are you filled Cell C4 & C5.

 

[StudentRed]

Are you filled Cell C4 & C5.

 

[StudentRed]

Hi maabadi,

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

 

[maabadi]

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]

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]

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:

=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