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

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
    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.
    – Tedinoz
    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
    – Tedinoz
    May 10 at 1:39

3 Answers 3

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.
2
  • 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.
    – Tedinoz
    May 12 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.
2
  • 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.
    – Tedinoz
    May 12 at 5:16
-2

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

thank you

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 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".
    – Tedinoz
    May 10 at 1:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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