CODEWITHSUNDEEP
excelexcel-formula
Kaish
Kaish

Reputation: 255

Using COUNTIF based on date value along the columns

I have a table that look like this:

Table 1 (starting A1)

03-02-2021 03-03-2021
role_1 name_1 IN OUT
role_1 name_2 IN IN

I need to create a second table that tells me the number of "IN" per day, per role. So it looks something like:

Table 2 (starting A1):

03-02-2021 03-03-2021
role_1 2 1

So essentially, for every day, count the number of "IN" I have. I tried doing this by doing the following formula:

=COUNTIFS(Table_1!A:A,A2,INDEX(table_1,,MATCH(B1,Table_1!1:1,0)),"IN")

Here, the COUNTIF has two parts:

Table_1!A:A,A2

This bit looks in the first column (A) of Table 1 to count the number of roles.

INDEX(table_1,,MATCH(B1,Table_1!1:1,0)),"IN"

An INDEX MATCH that looks in Table 1 and derives the relevant column by INDEX MATCHING on the specific date, and then counts the "IN"s.

While both of these work independently, they don't work together (thus not giving me the right answer). Together the formula just errors.

How should I be adjusting this to do what I want?

Upvotes: 1

Views: 469

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

You can use OFFSET formula to get the desired column. With your data following will work.

=COUNTIFS(Table_1!$A$1:$A$3,Table_2!$A2,OFFSET(Table_1!$A$1:$A$3,0,MATCH(Table_2!B$1,Table_1!$A$1:$D$1,0)-1),"IN")

Major difference in principle: it has OFFSET to evaluate the range in reference which is a volatile function.

Alternatively, you can use below approach (tested in Office 365) as well to use original formulas that you have posted.

=COUNTIFS(Table_1!$A$1:$A$3,Table_2!$A2,INDEX(Table_1!$A$1:$D$3,0,MATCH(Table_2!B$1,Table_1!$A$1:$D$1,0)),"IN")

Here, the difference being inside INDEX we feed complete data range i.e. Table_1!$A$1:$D$3 and argument for rows is passed as 0 which tells Excel that we are interested in whole column.

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

I made fake dataset in a worksheet named Table_1 like this:

enter image description here

Also, a second worksheet named Table_2 got this:

enter image description here

My formula in B2 is:

=SUMPRODUCT(--(Table_1!$C$2:$D$5="IN")*--(Table_1!$C$1:$D$1=Table_2!B$1)*--(Table_1!$A$2:$A$5=Table_2!$A2))

Just drag right and down and it should work.

This is how it works (for B2 as example):

  1. --(Table_1!$C$2:$D$5="IN") will create an array of 1 and 0 depending if cell value is ÌN or OUT. So it returns {1;0\1;1\1;0\0;1}
  2. --(Table_1!$C$1:$D$1=Table_2!B$1) will return an array of 1 and 0 of headers row if they are equal to date in column. So it returns {1;0}
  3. Both arrays from steps 1 and 2 are multiplied, so it returns {1;0\1;1\1;0\0;1} * {1;0} = {1;0\1;0\1;0\0;0}
  4. --(Table_1!$A$2:$A$5=Table_2!$A2) will return an array of 1 and 0 if row value is same role or not. So it returns {1\1\0\0}
  5. We get product from arrays from step 4 and 3, so {1;0\1;0\1;0\0;0} * {1\1\0\0} = {1;0\1;0\0;0\0;0}
  6. Final sum of {1;0\1;0\0;0\0;0} = 2

Hope this helps. You can see how it works with the button EVALUATE FORMULA in Excel, and see the arrays by yourself.

Upvotes: 1

Related Questions