how to sum an equatioin for a list of fields in excel-CodePudding

My equation looks like this

J2

=IF(AND(B2=D2,B2=1),1,0)

so clearly the cell under will be

J3 
=IF(AND(B3=D3,B3=1),1,0)

and so on

at the end of these cells, I have an equation to get the sum of all calculated values above

=SUM(J2:J101)

is there a way to build this equation without the need of making 100 cells before the sum

i mean can I do something like this?

=SUM(IF(AND(B2:B101=D2:D101,B2:B101=1),1,0))

CodePudding user response：

If I've understood you correctly then you should just be able to use a COUNTIFS ...

=COUNTIFS(B2:B101,1,D2:D101,1)

... if B has to be 1 then so does D and because you're returning 1 if true then a COUNTIFS yields the same result as a SUM in this scenario.