Question 245448 in Shopify Question posted in
The Shopify help center can be found here.

Shopify – Countifs Google Sheets on multiple columns: search for dates

I need to make a countifs function work, it has to match the number of orders received from a specific country during a month.

Hello! I’m trying to make a COUNTIFS function work: In sheet 1 I have a table containing a column with dates, one with order numbers and one with the country.

Column A Column B Column B
01/01/2023 ord #1 Italy
04/01/2023 ord #2 USA
05/01/2023 ord #3 Italy
09/01/2023 ord #4 USA

I need to fill another table in sheet 2 where the countries are listed in rows and months in columns and I need the funcion to count how many orders arrived on that month from that country.

Column A Column B
Italy 2
USA 2

I tried with this funcion:
=COUNTIFS(‘ORDINI SHOPIFY’!A:C;"Italy";’ORDINI SHOPIFY’!A:C;"/01/2023")

But the result is "0" for each cell. I’m not used to Google Sheets, I usually work with Excel and maybe there is something in the synthax that I didn’ get yet.

Thank you!

Tags:

2

Answers


  1. 0

    If your column A is text:

    =COUNTIFS(C:C,"Italy",A:A,"*/01/2023")

    Result:

    enter image description here

    If your column A is actual date:

    =ARRAYFORMULA(sum((C:C="Italy")*(month(A:A)=1)*(year(A:A)=2023)))

    Result:

    enter image description here

    Adapt to your ranges and formula separator.

    Login or Signup to reply.
  2. 0

    Here you have two options to generate the full chart with QUERY:

    =QUERY(A:C,"SELECT C,COUNT(B) WHERE A is not null group by C pivot MONTH(A)+1",1)

    enter image description here

    Or, if you need the full format of date, you can previously "convert" Column A to MM-YYYY (or as you need it):

    =QUERY({INDEX(TEXT(A:A,"MM-YYYY")),B:C},"SELECT Col3,COUNT(Col2) WHERE Col2 is not null group by Col3 pivot Col1",1)

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search