These are just a few ways to count and add numbers that match criteria. (There are many other ways also)

=SUMPRODUCT((A1:A20>5)*(A1:A20<16))

or

=SUMPRODUCT(--(A1:A20>5),--(A1:A20<16))

or

{=SUM((A1:A20>5)*(A1:A20<16))}

Push Ctrl, Shift and Enter to make this an array formula

{=SUM((A1:A20=5)+(A1:A20>16)+(A1:A20=18))}

Push Ctrl, Shift and Enter to make this an array formula

or

=COUNTIF(A1:A20,"=5")+COUNTIF(A1:A20,">16")+COUNTIF(A1:A20,"=18")

or

=SUMPRODUCT((A1:A20=5)+(A1:A20>16)+(A1:A20=18))

=SUMPRODUCT((A1:A10>3)*(A1:A10<6),A1:A10)

or

=SUMPRODUCT(--(A1:A10>3),--(A1:A10<6),A1:A10)

or

{=SUM((A1:A10>3)*(A1:A10<6)*A1:A10)}

Push Ctrl, Shift and Enter to make this an array formula

or

{=SUM(IF(A1:A10>3,IF(A1:A10<6,A1:A10)))}

Push Ctrl, Shift and Enter to make this an array formula

=SUMPRODUCT((A1:A10>3)+(A1:A10<6),A1:A10)

or

=SUMIF(A1:A10,">3")+SUMIF(A1:A10,"<6")

© Copyright
andrewsexceltips.net All Rights Reserved.