Forum Discussion
=NB.SI
I would like to know how to calculate the number of miles I had run every month of every year. I have tried different formulas such as =NB.SI(Sheet1!C1:Y1;"2017-03-*") (with the asterix) but it doesn't work.
| 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 | |
| Running | Miles | 5 | 6 | 5 | 5 | 5 | 5 | 5 | 6 | 7 | 5 | 5 | 5 | 6 | 7 | 10 | 5 | 6 | 6 | 5 | 6 | 6 |
Hi Claude,
SUMPRODUCT sums filtered data. For the data structured like this
the formula could be
=SUMPRODUCT((YEAR($B$3:$CC$3)=YEAR($A8))*(MONTH($B$3:$CC$3)=MONTH($A8))*$B$4:$CC$4)
If you open attached file the formula shall appear in your locale.
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.
