SOLVED

New Contributor

# COUNTIFS returns either #VALUE or 0

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!

7 Replies

# Re: COUNTIFS returns either #VALUE or 0

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

# Re: COUNTIFS returns either #VALUE or 0

@Sanne330 Try something this:

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

# Re: COUNTIFS returns either #VALUE or 0

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

# Re: COUNTIFS returns either #VALUE or 0

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

# Re: COUNTIFS returns either #VALUE or 0

You may edit the formula below to suit your range:

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

best response confirmed by Sanne330 (New Contributor)
Solution

# Re: COUNTIFS returns either #VALUE or 0

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

# Re: COUNTIFS returns either #VALUE or 0

@Starrysky1988Thank you so much! That works perfectly!