Mar 25 2022 06:39 AM - edited Mar 25 2022 08:10 AM
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!
Mar 25 2022 07:12 AM
To attach your sample file, simply click "browse" at the bottom of your post. Crucially, please explicitly state and explain your expected results.
Mar 25 2022 07:17 AM - edited Mar 25 2022 07:17 AM
Mar 25 2022 07:59 AM
@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())
Mar 25 2022 08:09 AM
@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).
Mar 25 2022 09:35 AM
You may edit the formula below to suit your range:
You can also play with the formula in the attached sample file.
Mar 25 2022 09:41 AM
SolutionMar 28 2022 07:34 AM
@Starrysky1988Thank you so much! That works perfectly!
Mar 25 2022 09:41 AM
Solution