Forum Discussion

Sanne330's avatar
Sanne330
Copper Contributor
Mar 25, 2022
Solved

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 scree...
Formulas and Functions
  • Starrysky1988's avatar
    Starrysky1988
    Mar 25, 2022
    Sumproduct function will work for you.
    You may write the formula as below.
    =Sumproduct((Kalender!$C:$J<=TODAY())*(Kalender!$B:$B=[@Naam]))
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 25, 2022

Sanne330 Try something this:

 

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

 

 

Sanne330's avatar
Sanne330
Copper Contributor
Mar 25, 2022

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

 

Resources