For conditional week wise count in Google Sheets, we can use a Query formula.

If you don’t want to specify any condition, then you can choose from the functions Query, Countif, or Countifs. In that case;

• The Countif function will be sufficient if you don’t want to consider the year part in the week wise summary.
• If you want to include the years too in the summary, then choose Countifs.
• The Query is useful in all cases.

So in this tutorial let’s learn how to use the Query function in conditional week-wise count in Google Sheets.

But to make you familiar with the Countif as well as Countifs in week wise count, I’ve included them too.

For our test, we can use the following basic production data.

Countifs in Conditional Week Wise Count in Google Sheets

Let’s start with Countif.

Related: How to Use All Google Sheets Count Functions [All 8 Count Functions].

If you want to count the items week wise, you can use the below Countif.

Steps:

In cell E2, enter the below Unique Array Formula to get the unique week numbers.

=ArrayFormula(
     unique(weeknum(A2:A18))
)

You May Like: How to Utilize Google Sheets Date Functions [Complete Guide].

Then in cell F2, use the following Countif formula.

=ArrayFormula(
     countif(
        weeknum(A2:A19),
        E2:E6
     )
)

In the Countif, we are permitted to use only one condition in the ‘normal’ way (without combining columns as well as criteria).

That one condition, we have already utilized to count the weeks from the date column.

I mean the condition in the above formula is the week numbers. So there is no scope of using any other condition.

Countif Formula Explanation:

Syntax:-

COUNTIF(
     range,
     criterion
)

range – weeknum(A2:A19)

criterion – E2:E6

As I have already mentioned above, since we have already used one condition, which is the week number itself, we can’t use one more condition within the Countif.

That means for conditional week wise count in Google Sheets, we can’t use the Countif. Then what about Countifs?

Single Condition – Item

Countifs works in conditional week wise count but has its own limitations. I’ll explain it in detail so that you can understand why we must use the Query function.

Let’s assume we want to count the item “Product A”, week wise. Here the condition is “Product A”.

Here we can use the Countifs as per the following syntax.

COUNTIFS(
     criteria_range1, criterion1,
     [criteria_range2, …], [criterion2, …]
)

Formula:-

=ArrayFormula(
     countifs(
        weeknum(A2:A19),E2:E6,
        B2:B19,"Product A"
     )
)

The above is an example to conditional week wise count with single criterion in Google Sheets.

Multiple Conditions – Item and Quality Check

Further, if you want to get the week wise count of the item “Product A” of which the quality check is “Passed”, the formula would be as below.

=ArrayFormula(
     countifs(
        weeknum(A2:A19),
        E2:E6,B2:B19,"Product A",
        C2:C19,"Passed"
     )
)

Other than week numbers, we can include years too in the formula. For that, we must replace the E2 week number formula with the below week number and year formula.

=ArrayFormula(
     UNIQUE({year(A2:A19),weeknum(A2:A19)})
)

That output of the above formula would be in two columns, i.e. in the range E2:F6.

Accordingly, we must modify our just above Countifs conditional week wise formula (above F2 formula) and use it in G2. Here is that.

=ArrayFormula(
     countifs(
        year(A2:A19),E2:E6,
        weeknum(A2:A19),F2:F6,
        B2:B19,"Product A",
        C2:C19,"Passed"
     )
)

But for a conditional week wise count summary report, we can use the Query function in a better way.

Must Read: Learn Query Function with Examples in Google Sheets.

Conditional Week Wise Count Using Query

In fact, there is no Weeknum function in Google Sheets Query. So here also we should use the WEEKNUM spreadsheet function to get week numbers from dates.

Unlike in Countif and Countifs, here we are going to use the Weeknum formula (formula in E2) within the Query as a ‘data’, not in any helper column.

Syntax:

QUERY(
     data,
     query,
     [headers]
)

Query Data – Years and Week Numbers

If we consider both the weeks and years, the query ‘data’ will be as below (a four column output).

=ArrayFormula(
     {YEAR(A2:A19),weeknum(A2:A19),B2:C19}
)

Here we are not using the Unique function with the ‘data’ (please refer to the use of Unique in Countifs above).

Because in the Query grouping the duplicate year and week numbers will be automatically got eliminated.

We will use the above formula within Query.

Here is my first Query formula for conditional week wise count in Google Sheets (which would return the below table).

=query(
     ArrayFormula({YEAR(A2:A19),weeknum(A2:A19),B2:C19}),
     "Select Col1,Col2,Col3,count(Col3) group by Col1,Col2,Col3 label Col1'Year', Col2'Week',Col3'Item'"
)

What the above formula is doing is;

It counts the items based on the year and week and the result will be a table as above.

In the above conditional week wise count formula in Google Sheets, I am going to use one more condition.

I just want the count of the items “Passed” (quality check).

For that before the “Group By” clause, insert the condition within the “Where” clause as below in Query.

where Col4 ='Passed'

So the formula would be;

=query(
     ArrayFormula({YEAR(A2:A19),weeknum(A2:A19),B2:C19}),
     "Select Col1,Col2,Col3,count(Col3) where Col4 ='Passed' group by Col1,Col2,Col3 label Col1'Year', Col2'Week',Col3'Item'"
)

Result:

Related: What is the Correct Clause Order in Google Sheets Query?

Why I’ve use col4 instead of col3 (“quality check” is the third column in the source data)?

Because the column numbers in the Query data are as follows.

Column 1 = Years
Column 2 = Weeks

The below two columns are the last two columns in our source data as it is.

Column 3 = Item
Column 4 = Quality Check

Additional Tips – Open Range Data

The above formula is for the range A2:C19.

I mean, you can see the use of the range A2:A19, B2:C19 etc. in the formula.

How to make them open like A2:A?

If you enter the Query Data, i.e. ARRAYFORMULA({YEAR(A2:A),weeknum(A2:A),B2:C}), in any cell, you will notice the issue. The blank rows too have values like 52 in week number column and 1899 in the year column.

This may or may not cause issues depending on the criteria used in Query.

So, in an open range, use the below formula as the Query data.

ARRAYFORMULA(if(len(A2:A),{YEAR(A2:A),weeknum(A2:A),B2:C},))

That’s all about conditional week wise count in Google Sheets. Enjoy!

Resources:

• How to Create A Weekly Summary Report in Google Sheets.
• Calendar Week Formula in Google Sheets to Combine Week Start and End Dates.
• Summarize Data by Week Start and End Dates in Google Sheets.
• How to Count Orders Per Week in Google Sheets [Formula Examples].