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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

maabadi

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

Hi maabadi,

It seems like this too returns #VALUE!
 

StudentRed

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

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Are you filled Cell C4 & C5.
 

StudentRed

New Member
Joined
Dec 21, 2020
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi maabadi,

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

maabadi

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

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