Countif formula with two criterias

[masben]

Hi,

I have a data tabel in which I would like to count the number of "0" enteries per month. In colum A I have dates, daily from a few years back - one row per day. In Colum B to F I have enteries "0" or "1"

Then I have a summary were I caculate the number of "0" per month. I have used the countif formula and for each month changed the rows to match the dates for that specific month.

Now I would like to set up the formula so that I don't need to change the rows range in the formula when I add a new month. The countif should count enteries if they are "0" and dates are matched. Se example file

Many thanks!
//Martin

[Spencer101]

Hi Martin,

Try the below formula in cell G4 and copy down.

=SUMPRODUCT((A$4:A$123>=F4)*(A$4:A$123<=EOMONTH(F4,0))*($B$4:$D$123=0))

[masben]

Thanks Spencer,

Works perfect!

Many thanks!
/Martin

Hi Martin,

Try the below formula in cell G4 and copy down.

=SUMPRODUCT((A$4:A$123>=F4)*(A$4:A$123<=EOMONTH(F4,0))*($B$4:$D$123=0))

[Spencer101]

Not a problem Martin. Happy to help.