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

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

how do i go about this?

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$75),$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$75))

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

On Nov 19, 12:10*am, Liv wrote:
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

how do i go about this?

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

"Pete_UK" wrote:

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$75),$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$75))

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

On Nov 19, 12:10 am, Liv wrote:
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

how do i go about this?

.

=SUMPRODUCT(($A$2:$A$7=B$10)*($B$2:$B$7=5)*($B$2: $B$77),$B$2:$B$7)
--
David Biddulph

"Liv" wrote in message
...
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

"Pete_UK" wrote:

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$75),$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$75))

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

On Nov 19, 12:10 am, Liv wrote:
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

how do i go about this?

.