SOLVED

COUNTIFS returns either #VALUE or 0

New Contributor

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).

 

1.PNG2.PNG

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->

3.PNG4.PNG

 

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!

7 Replies

@Sanne330 

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

@Sanne330 Try something this:

 

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

 

 

@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())

 

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

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).

@Sanne330 

You may edit the formula below to suit your range: 

SumOfCounts.png

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

best response confirmed by Sanne330 (New Contributor)
Solution
Sumproduct function will work for you.
You may write the formula as below.
=Sumproduct((Kalender!$C:$J<=TODAY())*(Kalender!$B:$B=[@Naam]))

@Starrysky1988Thank you so much! That works perfectly!