Count & Add with Criteria
These are just a few ways to count and add numbers that match criteria. (There are many other ways also)
Count numbers that match both (all) criteria.
=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
Count numbers that match either (any) criteria.
{=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))
Add numbers that match both (all) criteria.
=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
Add numbers that match either (any) criteria.
=SUMPRODUCT((A1:A10>3)+(A1:A10<6),A1:A10)
or
=SUMIF(A1:A10,">3")+SUMIF(A1:A10,"<6")