what to use

[Rockbear]

I have a sheet column A true L

A B C D E F
G H
Date Id number reciver Ref Supplier from/to ID Type Net
weight

I J K L
Volume Size Weight Amount/cost


In column E is the supplier listed, there are about 283 suppliers listed in
collum E
IN column K is the weight of the shipment listed as weight

I have 12 different weightgroups to sort the suppliers shipments in :
From 0 to 100 kg, 101 to 300 kg, 301 to 500, 501 to 1000, 1001 to 1500, 1501
to 2000, 2001 to 2500, 2501 to 5000, 5001 to 10000, 10001 to 15000, 15001 to
20000, 20001 to 99999, and last 100000 to unlimited

If the supplier in is found in the E coulmn and the weight is between 0 and
100 it need to return the number of weights that are between 0 and 100. and
so on for the different 12 weight groups, pr supplier (283 different)

Have made this sheet, where the result from the transporters report will get
sorted

A B C D E F G H I
J K L
Suppliers name 100 300 500 1000 1500 2000 2500 5000 10000 15000 20000
M N
99999 NUMBER OF SHIPMENTS

I tried with a nested Number.if sentence, but it became sooo long and I did
not find where I made my mistake, and my knowledge is limited. I had help
from someone here with a vlookup function and that worked like a charm,
So you smart guys out there can you help me?? If you understand what i hope
to manage. The report from the transportcompanies is big, the biggest is
20164 rows of transports from almost all suppliers

I have just 10 different transporters that transports from 283 suppliers

 

[ShaneDevenshire]

Hi,

you need a formula something like this

=SUMPRODUCT(--($E$1:$E$16="SupplierName"),--($K$1:$K$16>=A$1),--($K$1:$K$16<=B$1))

You will need to modify it to reflect sheet names and the real range. You
can also replace the "SupplierName" with a cell reference. Note I have used
A1 and B1 assuming that the weight categories are on row 1 in you example of
the output sheet. Also you will need to modify the last formula on the right
when you copy it because is should not have the K1:K16<=N1 part for the last
group.

Hope this gets you started, please click the Yes button if this helps

 

[Rockbear]

Could not make this work

lets see if I can explaine better
in the report from the transporter there is only 2 colomns i need info from

E contains the name of supplier, this supplier have sent xx shipments to us
with various weights, here i CAN USE =NUMBER.IF(E:E;N2) (then I get how many
shipments this supplier has sent) (N= has list of suppliers from N2 to N285)

K contains the weight of the shipmet, from this supplier and I can get the
totalt weight this supplier has sent by using ==SUMIF(E:E;N2;K:K)

The first supplier in E2 is Mini press, and in K the weight is 80 then I
want the Q2 to say 1 (Q= the weightgroup under 100 kg)
The same supplier is also in E121 with weight 120 kg then I want the R2 to
say 1 ( because the weight is between 101 and 300)


N2= name of supplier, O2=total weight from this supplier, P2=total weight
sent by supplier Q2= how many shipments this supplier has sent that are from
0 to 100 kg R2=how many shipments this supplier has sent that are from 101 to
300 kg, S2=301 to 500, T2=501 to 1000, U2= 1001 to 1500, V2=1501 to 2000,
W2=2001 to 2500, X2=2501 to 5000, Y2=5001 to 10000, Z2=10001 to 15000, AA=
15001 to 20000, AB= 20001 to 99999

THE PROBLEM STARTS WHEN i HAVE TO SORT THE SUPPLIERS WEIGHTS INTO THE 12
WEIGHTGROUPS

Q2
=IF(E:E=N2 and K(in the same row) are between 0 and 100 it should say 1, but
if it finds N2 in another row and the value in the K are between 0 and 100 it
sould say 2

In a perfect world i could have put
sumif(E:E=N2 and K:K larger than 0 or smaller than 100) in Q2

then it would have found all rows with N2 as a supplier that was bewteen 0
and 100 kilo (kg)

Rock