Excel - Using start_date and end_date values with {=SUM(IF ...)}-CodePudding

I am trying to count data in a range named 'democracy' based on a date start and date end. I have attempted to get this done, but can't seem to fit the pieces together

Component 1

In the SumIf I have:

{=SUM(IF((democracy_highlighted=1) (democracy_shown=1),1,0))}

The named range democracy contains 2 columns, "highlighted" and "shown", this works in checking both the columns for the value of 1 to be present in one of them.

Component 2

In the countif I have:

=COUNTIFS(democracy_shown,1,DateList,">="&$B$3, DateList,"<="&$B$4)
=COUNTIFS(democracy_highlighted,1,DateList,">="&$B$3, DateList,"<="&$B$4)

This shows data between the start and end dates.

What I need to do is use the {=SUM(IF ...)} component, and limit the results based on start date and end date values?

CodePudding user response：

You could use SUMPRODUCT - something like:

=SUMPRODUCT(SIGN(((democracy_highlighted=1) (democracy_shown=1)))*(DateList>=$B$3)*( DateList<=$B$4))