COUNTIFS returns either #VALUE or 0

[Sanne330]

Hello!

I'm trying to count how many dates are in the red per "EVB'er". Red meaning the date is today or before today. Column C to J are formatted as dates.

Let's call the table in my first screenshot, the one with the colours, Dates. Let's call the table in my second screenshot EVB.

I want to count every red cell (date is today or before today) in Dates!columns C through J (first screenshot) in every row where the value in Dates!column B (first screenshot) is the same as EVB!column A (second screenshot), for example "Clarissa Schneider". The result would be an integer and would be displayed in EVB!column B (second screenshot).

I'm using Excel in Dutch, so I'll give both versions of my formula, Dutch and English.

Dutch: =AANTALLEN.ALS(Kalender!$C:$J;"<="&VANDAAG();Kalender!$B:$B;"="&[@Naam])
English: =COUNTIFS(Kalender!$C:$J,"<="&TODAY(),Kalender!$B:$B,"="&[@Naam])

This one returns #VALUE

Dutch: =AANTALLEN.ALS(Kalender!$B:$J;"<="&VANDAAG();Kalender!$B:$J;"="&[@Naam])
English: =COUNTIFS(Kalender!$B:$J,"<="&TODAY(),Kalender!$B:$J,"="&[@Naam])

This one returns 0

If I use a COUNTIFS with just on of the two criteria, I get this:

<- Just name/Just date in red->

What do I need to do/change to effectively count the dates in red per "EVB'er"?

I'd love to add my file, but I don't see how.

Thank you!

[#2]

@Sanne330 

To attach your sample file, simply click "browse" at the bottom of your post. Crucially, please explicitly state and explain your expected results. 

[#3]

@Sanne330 Try something this:

=COUNTIFS(INDEX(Table1,MATCH([@naam],Table1[evb],0),),"<="&TODAY())

[#4]

@Riny_van_EekelenThank you! I got it to work to count all the "red dates" in a single row, but I need to count multiple rows per "EVB'er" and add those up. See the picture in my opening post.. "EVB'er" is column B. Column A contains a different name on each row, but column B contains the same name in multiple rows and I want to know how many dates are red over multiple rows that each contain the same value for column B.

This is how my formula looks when I got it to work for counting a single row:

Dutch:

=AANTALLEN.ALS(INDEX(Kalender!B:K;VERGELIJKEN([@Naam];Tabel_Planningskalender[EVB''er];0)+1;);"<="&VANDAAG())

English:

=COUNTIFS(INDEX(Kalender!B:K,MATCH([@Naam],Tabel_Planningskalender[EVB''er],0)+1,),"<="&TODAY())

[#5]

@Twifoounfortunately I do not see "browse" at the bottom of my post.

Let's call the table in my first screenshot, the one with the colours, Dates. Let's call the table in my second screenshot EVB.

I want to count every red cell (date is today or before today) in Dates!columns C through J (first screenshot) in every row where the value in Dates!column B (first screenshot) is the same as EVB!column A (second screenshot), for example "Clarissa Schneider". The result would be an integer and would be displayed in EVB!column B (second screenshot).

[#6]

@Sanne330 

You may edit the formula below to suit your range: 

You can also play with the formula in the attached sample file. 

[#7 · Accepted solution]

Sumproduct function will work for you.
You may write the formula as below.
=Sumproduct((Kalender!$C:$J<=TODAY())*(Kalender!$B:$B=[@Naam]))

[#8]

@Starrysky1988Thank you so much! That works perfectly!