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$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

On Nov 19, 12:10*am, Liv <L...@discussions.microsoft.com> 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$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
>
> On Nov 19, 12:10 am, Liv <L...@discussions.microsoft.com> 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$7<7),$B$2:$B$7)
--
David Biddulph

"Liv" <(E-Mail Removed)> wrote in message
news:60C3763D-4FFE-49EE-854D-(E-Mail Removed)...
> 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$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
>>
>> On Nov 19, 12:10 am, Liv <L...@discussions.microsoft.com> 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?
>>
>> .
>>