0

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

I need to add in additional conditions:

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"))
Improve this question
6
  • 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?
    – Tedinoz
    Commented May 7, 2022 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!
    – Stuart Bell
    Commented May 9, 2022 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
    – Stuart Bell
    Commented May 9, 2022 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.
    – Tedinoz
    Commented May 10, 2022 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
    – Tedinoz
    Commented May 10, 2022 at 1:39

3 Answers 3

Reset to default
0

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.
Improve this answer
2
  • Thank you, I tried to replicate this query but failed Formula test! B5 docs.google.com/spreadsheets/d/…
    – Stuart Bell
    Commented May 10, 2022 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.
    – Tedinoz
    Commented May 12, 2022 at 5:17
0

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:

Data Layout

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.
Improve this answer
2
  • I tried to replicate this in Formula test! B4 but failed! docs.google.com/spreadsheets/d/…
    – Stuart Bell
    Commented May 10, 2022 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.
    – Tedinoz
    Commented May 12, 2022 at 5:16
-2

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

Improve this answer
3
  • 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
    – Stuart Bell
    Commented May 9, 2022 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
    – Stuart Bell
    Commented May 9, 2022 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".
    – Tedinoz
    Commented May 10, 2022 at 1:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.