COUNTIFS with multiple ifs and within date-range

[pasjauo]

Hey all you geniuses,

I'm really struggling with a COUNTIFS formula, and I hope you can help.

Date

Error type 1

Error type 2

Error type 3

Size

<tbody>
</tbody>

Columns are A,B,C,D,E in this example

This is my setup. The premise is that the user will choose some problems which will show in the 3 error type columns. What is tricky is that the same problem can be present in error type 1 in one row and then in error type 3 in the next row.

What I have done so far (where the date was not an issue) was to use COUNTIFS to filter in the size to each known error (i have around 23 errors). Something like this:

=COUNTIFS($B:$B;"problem 1";$E:$E;"Large")+COUNTIFS($C:$C;"problem 1";$E:$E;"Large")+COUNTIFS($D:$D;"problem 1";$E:$E;"Large")

It might not be pretty but it works at least.

Here comes the tricky part. I have added the date column and would now want to filter each month out so i can make statistics for each month instead of just the total.

I have tried to add two criterias (above for instance 1/1/2018 and below 1/31/2018). This does not work.

=COUNTIFS($B:$B;"problem 1";$E:$E;"Large";$A:$A;">=1/1/2018";$A:$A;"<=1/31/2018")

I can manipulated my data so there is definitely one match on that formula if it had worked. It does show a 0 though. My language in excel is not english, meaning the syntax might not look english but it is the correct form.

What am I doing wrong?

 

[pasjauo]

I have been digging more in to it myself, since I really dont know why it doesn't work.

=COUNTIFS($B:$B;"problem 1";$E:$E;"Large";$A:$A;"=1/1/2018")

The above code works if the date is exactly 1/1/2018. If I do a <=1/2/2018 it fails again. So it seems it is the <> that creates the problem.

 

[Gerald Higgins]

Try replacing

">=1/1/2018"

with

">="&(date(2018,1,1))

 

[pasjauo]

Try replacing

">=1/1/2018"

with

">="&(date(2018,1,1))

Same problem occurs. If I do like you say it doesnt work. If i remove > and set the date to the right date in the column it works. So the syntax works but there is something wrong with the <=> apparently.

 

[Gerald Higgins]

Presumably you followed my suggestion for ALL the dates in your COUNTIFS formula, not just one ?

 

[pasjauo]

Presumably you followed my suggestion for ALL the dates in your COUNTIFS formula, not just one ?

Yes. To make sure I did not make any errors i simplified the formula so it for instance only searched for >=1/1/2018 if you know what i mean. Even a COUNTIF with only 1 criteria didnt work, so its not because I forgot something in a long formula unfortunately.

 

[Gerald Higgins]

Can you please post the latest version of the formula that you have ?

 

[pasjauo]

Can you please post the latest version of the formula that you have ?

This is the exact formula i use (with no translation):

=TÆL.HVISER(Automater1!$K:$K;"="&A24;Automater1!$A:$A;">="&(DATO(2018;1;1)))

I guess most of it makes sense translation wise but let me know if you have troubles. Most different is probably the ; instead of the english , to seperate.

 

[Gerald Higgins]

Hmm . . . what does this mean . . .

TÆL.HVISER

Is it your local equivalent of
COUNTIFS
?

 

[Gerald Higgins]

Assuming that DOES mean the same as COUNTIFS, then this formula works as expected . . . .

=COUNTIFS(Automater1!$K:$K,"="&A24,Automater1!$A:$A,">="&DATE(2018,1,1))

In other words, counts how many rows on the "Automater1" sheet have a date in column A that is greater than 1/1/2018, AND have an entry in column K that exactly matches the value in cell A24.
A24 refers to the same sheet that the formula is entered into, and not "Automater1".