Forum Discussion
=NB.SI
Thank you for your fast and clear answer. It works great!
I have another question if I may. I would like to count the number of time every month appears in my schedule. In the example below, I already put the answer in red but I can't find the right formula. Any idea?
| 2017 | 20 mars | 24 mars | 27 mars | 03 avr | 07 avr | 10 avr | 13 avr | 18 avr | 21 avr | 24 avr | 27 avr | 01 mai | 04 mai | 08 mai | 11 mai | 2018 | 22 janv | 25 janv | 29 janv | 01 févr | 02 févr | 05 févr | 06 févr |
| Month | Days/month | ||||||||||||||||||||||
| mars 2017 | 3 | ||||||||||||||||||||||
| avr 2017 | 8 | ||||||||||||||||||||||
| mai 2017 | 4 | ||||||||||||||||||||||
| juin 2017 | 0 | ||||||||||||||||||||||
| juil 2017 | 0 | ||||||||||||||||||||||
| août 2017 | 0 | ||||||||||||||||||||||
| sept 2017 | 0 | ||||||||||||||||||||||
| oct 2017 | 0 | ||||||||||||||||||||||
| nov 2017 | 0 | ||||||||||||||||||||||
| déc 2017 | 0 | ||||||||||||||||||||||
| janv 2018 | 3 | ||||||||||||||||||||||
| févr 2018 | 4 | ||||||||||||||||||||||
| mars 2018 | 0 | ||||||||||||||||||||||
| avr 2018 | 0 |
Hi Claude,
Same SUMPRODUCT but without multiplying on Miles
=SUMPRODUCT((YEAR($B$3:$CC$3)=YEAR($A8))*(MONTH($B$3:$CC$3)=MONTH($A8)))
In more details, first it returns an array where 1 (aka TRUE) appears for each entry which meets your criteria (certain year and month) and 0 (aka FALSE) otherwise. For March 2017 it looks like
(1,1,1,0,0,0,0,0..}
(as many zeroes as values in your record after the March 2017).
If you SUM this array (what SUMPRODOCT finally do) you receive the count. If before that you multiply it on Miles before SUM (as in previous case) you'll have total for miles in a month.