COUNTIFS using multiple criteria in single formula

问题:

Column A has Apples,Oranges, Pears multiple times.
Column B has the count against them (note, some of these may be blank).

I'm looking for a formula to count just Apples and Oranges where their count is neither blank nor 0.

I tried the formula below, but I get the count of Apples only:

=COUNTIFS(A1:A21,{"Apples","Oranges"},B1:B21,">0")

回答1:

You need to use your formula in a sum function like this:

=SUM(COUNTIFS(A1:A21,{"Apples","Oranges"},B1:B21,">0"))

The reason is, that your function creates an array with the counts of apples and oranges respectively. You have to sum the elements in this array to get your desired result.