how to count the number of times text occurs based on condition

L

Liv

scenario:

i would like to return two numbers, the count of times each fruit occurs
when <5 and >=5, and the sum of the value when each fruit is <5 and >=5

FRUIT \$\$\$
apple 1
orange 2
pear 1
pear 8
pear 5
apple 6

should return

apple orange pear
sum <5 1 2 1
sum >=5 6 0 13

apple orange pear
count <5 1 1 1
count >=5 1 0 2

P

Pete_UK

Assume your data is in A2:B7, with headings in row 1, and that your
first table has headings starting in B10 and the second table has
headings starting in B14. Put this in B11:

=SUMPRODUCT((\$A\$2:\$A\$7=B\$10)*(\$B\$2:\$B\$7<5),\$B\$2:\$B\$7)

and this in B12:

=SUMPRODUCT((\$A\$2:\$A\$7=B\$10)*(\$B\$2:\$B\$7>=5),\$B\$2:\$B\$7)

then copy across to C1112.

Similarly, put this in B15:

=SUMPRODUCT((\$A\$2:\$A\$7=B\$14)*(\$B\$2:\$B\$7<5))

and this in B16:

=SUMPRODUCT((\$A\$2:\$A\$7=B\$14)*(\$B\$2:\$B\$7>=5))

and copy both of these across.

Hope this helps.

Pete

L

Liv

Thank you Pete! Is there a way to fit two conditions into the equation?
For instance if I were asked to return whatever is >=5 AND <7

D

David Biddulph

=SUMPRODUCT((\$A\$2:\$A\$7=B\$10)*(\$B\$2:\$B\$7>=5)*(\$B\$2:\$B\$7<7),\$B\$2:\$B\$7)