Several ALS functions in matrix

[coldsteel404]

Hello,

I'm using Excel to sort through a list of data to check how many complaints there were in 2012, 2013 and 2014. For this I used the following formula: =AANTAL.ALS(A3:A398;F3:F5) and it works just fine.

Now what I want to do is check who managed those complaints in 2012, 2013 and 2014. So I want Excel to calculate me how many complaints mss X, Mr Y and mss Z handled in 2012, 2013 and 2014. I can easily do this for all years, but how can I make it so that it's calculated for each year sepperatly?

Total cases in 2012 =AANTAL.ALS(A3:A398;F3:F5)
Total cases in 2013 =AANTAL.ALS(A3:A398;F3:F5)
Total cases in 2014 =AANTAL.ALS(A3:A398;F3:F5)

Total cases in 2012 handled by
Mss X
Mr Y
Mss Z

Etc.

I hope it's clear what I'm trying to do and someone is willing and able to lend me a hand

Kind regards,

 

[Aladin Akyurek]

=AANTAL.ALS(A3:A398;F3:F5)

does not give you a single count. For that you need something like:

=SOMPTODUCT(AANTAL.ALS(A3:A398;F3:F5))

FDoes the following...

=AANTALLEN.ALS(NameRange;Name;YearRange;Year;ComplaintsRange;"?*")

determine say Mr Y's (a name) count as intended?

 

[konew1]

Google tells me that =AANTAL.ALS is Dutch for =COUNTIF function.
I suggest a pivot table or use =COUNTIFS() function
What version of excel do you have?

 

[coldsteel404]

{=AANTALLEN.ALS(A3:A270;F7:H7;C3:C270;E8:E14)} worked like a charm, thanks a lot!!

 

[Aladin Akyurek]

{=AANTALLEN.ALS(A3:A270;F7:H7;C3:C270;E8:E14)} worked like a charm, thanks a lot!!

Control+shift+enter, not just enter:

=SUM(AANTALLEN.ALS(A3:A270;F7:H7;C3:C270;E8:E14))