# Importrange with multiple criteria

Looking to countifs data from another workbook with multiple criteria.

So far I have

``````=COUNTIFS(IMPORTRANGE("URL", "Data save!\$A\$2:\$A\$5011"),">=01/04/2022")
``````

This returns a count of every time a date is found `>=01/04/2022`

``````A2:A,<=today
G2:G, "Partner"
``````

I'm not sure if this is even possible?

Slightly different scenario as I am experimenting with different ways to do this...

I am now trying

=SUMPRODUCT((Data_save!A2:A>=\$A\$22)*(Data_save!A2:A<=\$A\$23))

This us searching within the same workbook and returns the required dates :)

When I add another range and criteria it returns #N/A despite having data in the required fields. This is the formula I am using for this: =SUMPRODUCT((Data_save!A2:A>=\$A\$22)(Data_save!A2:A<=\$A\$23)(Data_save!G:G="Partner"))

• Hi. `COUNTIFS` is specifically designed for multiple criteria. What happened when you tried to solve this with `COUNTIFS` or any of the methods described in This question of yours? May 7 at 23:49
• Thanks for the comment. Checking back at the formula I used =COUNTIFS(IMPORTRANGE("URL", "Data save!\$A\$2:\$A\$5011"),">=01/04/2022") returns a value of 820 which is correct. I have edited the original question with another way I am attempting this but also failing! May 9 at 11:46
• I thought this may work but doesn't accept the 2nd criteria =COUNTIFS(IMPORTRANGE(("URL", "Data_save!\$A\$2:\$A"),">=01/04/2022", "<=09/04/2022")) it returns #error formula parse error May 9 at 11:52
• Hi. A few things. 1) There's a general rule of one question, one topic. So it would be appropriate for you to raise a new question for the discussion of `sumproduct` with multiple criteria. 2) when introducing a scenario, you need to provide the user with an explanation, some data and an expected outcome. I know you know about this, so I'm just reminding you. This would include an example of the values in cells A22 and A23. May 10 at 1:23
• Oh... your formula for `sumproduct` doesn't seem to reply on `IMPORTRANGE`. Not a big issue, but confirmation that you're looking at a separate issue May 10 at 1:39

QUERY

The query needs to reference the `IMPORTRANGE` , though in this case, the entire range can be covered in one statement.

`=query({{"Date","Email","Name","Partner"};importrange(B2,"Data save!\$A\$2:\$D\$")},"select Col1, Col2, Col3, Col4 where Col1 <= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and Col4 contains 'Partner'")`

• `importrange(B2,"Data save!\$A\$2:\$D\$")` - Cell B2 contains the spreadsheet url.
• Thank you, I tried to replicate this query but failed Formula test! B5 docs.google.com/spreadsheets/d/… May 10 at 20:42
• My apologies... `IMPORTRANGE` imports cells from a specific spreadsheet. Cell B2 is the url for that spreadsheet. In your test, both the formula and "Data Save" are in the same spreadsheet. May 12 at 5:17

The range for date is not the same as the range for the "partner" designation. So, you need to identify separate criteria ranges for each element of your parameters

Say you data looks like this:

Then your formula needs to address both Column A (Dates) AND Column D (Partner...)

`=countifs(importrange(B2,"Data save!\$A\$2:\$A"),"<="&TODAY(),importrange(B2,"Data save!\$D\$2:\$D"),"=Partner*")`

• `importrange(B2,....)` - Cell B2 contains the spreadsheet url.
• I tried to replicate this in Formula test! B4 but failed! docs.google.com/spreadsheets/d/… May 10 at 20:22
• My apologies... `IMPORTRANGE` imports cells from a specific spreadsheet. Cell B2 is the url for that spreadsheet. In your test, both the formula and "Data Save" are in the same spreadsheet. May 12 at 5:16

Try using query function (use "Select * where ", and you can add multiple criteria with one query)

thank you

• Thank you, I'm investigating the query function as you say. It is new to me so I have some reading up to do first May 9 at 15:53
• I tried this =QUERY(Data_save!\$A\$1:\$G,"select * where A > date '"&TEXT(A2:A,"2022/04/01")&"' and A <= date '"&TEXT(A2:A,"2022/05/09")&"'",1) but it returns #value! error - it doesn't have the G:G = "Partner" part yet as I can't get this bit to work with query May 9 at 18:48
• you can add multiple criteria with one query. This goes without saying. The issue is "what is the syntax to reference the parameters in the OP question?" - "<=today()" and "=Partner". May 10 at 1:37