Countif Multiple Criteria Google Sheets

[MilanB]

Hello. I tried to make a formula for counting multiple criteria, but I need some assistance. I need to count how many contracts were made by an employee, exist different types of contracts marked with some code (120, 121, 122...). But if employees make many contracts of the same code and for the same company needs to be counted only as 1. For example, as you can see in the image, an employee with the name "ANA" made twice contract "120" for "COMPANY 1" but this needs to be counted as 1, not 2. Results need to be written in the table below.

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER($C$4:$C$11,($A$4:$A$11=$A18)*($B$4:$B$11=B$17))))

 

[MilanB]

Thanks. That formula works, only instead of 0, it shows #N/A.

I now see that I miss one important thing. Some of the contracts have different variants, for example, 120, 120a, 120b... they all need to be counted as 120.

 

[Fluff]

Are you doing this in Excel or some other software?

 

[MilanB]

I use Google Sheets.

 

[Fluff]

In that case you should make that clear (preferably in the thread title) as Excel & Sheets are not the same.
Also questions regarding Sheets should be posted in the General Discussion & Other Applications section of the board.

I have done that for you this time.

 

[Fluff]

You could try

=IFERROR(ROWS(UNIQUE(FILTER($C$4:$C$11,($A$4:$A$11=$A18)*(LEFT($B$4:$B$11,LEN(B$17))=B$17&"")))),0)

but I don't know if that works in Sheets.

 

[MilanB]

Sorry, didn't know that I need to write in a different section of the forum.

This formula doesn't work, only gives the answer "TRUE".

 

[Fluff]

In that case unfortunately I cannot really help as it works in Excel & I don't know much about Sheets.
That said I don't see how the formula can return True.