fish number fish weight sum3-4 sum 4-5
31457 3.5
45367 4.5
34289 3.5
sum 3-4 should return sum of the two rows with between 3 and 4 (230111)
and sum 4-5 shold return sum sum of the row with 4.5 (45367)
fish number fish weight sum3-4 sum 4-5
31457 3.5
45367 4.5
34289 3.5
sum 3-4 should return sum of the two rows with between 3 and 4 (230111)
and sum 4-5 shold return sum sum of the row with 4.5 (45367)
If your data is in the cells a4:b6 try something like the formula below
=SUMPRODUCT((B4:B6<5)*(B4:B6>4)*(A4:A6)*(b4:b6)
It is not clear if you require < and > or <+ and >= for your parameters
Regards
Dav
Last edited by Dav; 03-07-2006 at 06:56 AM.
sorry i had to fix a number here - its ok now
"> fish number fish weight sum3-4 sum 4-5
> 31457 3.5
> 45367 4.5
> 34289 3.5
>
> sum 3-4 should return sum of the two rows with between 3 and 4 (230111)
> and sum 4-5 shold return sum sum of the row with 4.5 (204151)
>
>
=SUMPRODUCT(--(A1:A100>=3),--(A1:A100<4)
etc.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"farmer" <[email protected]> wrote in message
news:[email protected]...
> fish number fish weight sum3-4 sum 4-5
> 31457 3.5
> 45367 4.5
> 34289 3.5
>
> sum 3-4 should return sum of the two rows with between 3 and 4 (230111)
> and sum 4-5 shold return sum sum of the row with 4.5 (45367)
>
>
If I understand correctly, you want to multiply the number of fish by the
wieght and categorise the total weight by individual fish weight. It's not
clear what you want to do with a fish weighing exactly 4, so I've assumed
3-4 includes 4.
I've assumed your data as posted occupies A1:D4
In C2
=IF(AND(B2>3,B2<=4),A2*B2,0)
Copy down the column
In D2
=IF(AND(B2>4,B2<5),A2*B2,0)
Copy down the column
At the bottom of columns C & D, sum the cells above eg
In C101
=SUM(C2:C100)
In D101
=SUM(D2:D100)
--
Ian
--
"farmer" <[email protected]> wrote in message
news:[email protected]...
> sorry i had to fix a number here - its ok now
>
> "> fish number fish weight sum3-4 sum 4-5
>> 31457 3.5
>> 45367 4.5
>> 34289 3.5
>>
>> sum 3-4 should return sum of the two rows with between 3 and 4 (230111)
>> and sum 4-5 shold return sum sum of the row with 4.5 (204151)
>>
>>
>
>
thanks a lot all of you
"Bob Phillips" <[email protected]> skrev i melding
news:%[email protected]...
> =SUMPRODUCT(--(A1:A100>=3),--(A1:A100<4)
>
> etc.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "farmer" <[email protected]> wrote in message
> news:[email protected]...
>> fish number fish weight sum3-4 sum 4-5
>> 31457 3.5
>> 45367 4.5
>> 34289 3.5
>>
>> sum 3-4 should return sum of the two rows with between 3 and 4 (230111)
>> and sum 4-5 shold return sum sum of the row with 4.5 (45367)
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks