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]

Hmm . . . what does this mean . . .


Is it your local equivalent of
COUNTIFS
?

Yes, indeed.

I have probably reached some sort of solution to why it didnt work.

My initial date im testing is input into the cell via a userform. The format of that cell is apparently not the same as if i write the date myself. So the formula actually works, but my vba code to input the date in to the cell does something strange.

I'm just using a Date_box.Value = Date premise.

Strange problem tbh.