First I want to say thank you for all the help everyone is here. Now for my
question. Still helping my husband do his spreadsheet for work with all his
warehouses. This is what I have:
=SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21)

where E12:E21 is for the Material Type
B3 is material type that I want to get a total number on
A12:A21 is how many of that type

Now I can't figure out the other half of this. Most of the material type has
a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to
add and then divide by 2 so it will give me an amount as if it were a 4x8.
But I need a calculation that will give me 1 number to represent a total
number of all. I hope this makes sense. The calculation as I have it, give me
a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of
the 4x4 in a 4x8 slot so they want to know how many 4x8's they have...

Please help and if its confusing, I can try again to explain it.
krista

Try something like this:

=SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2))

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
First I want to say thank you for all the help everyone is here. Now for
my
question. Still helping my husband do his spreadsheet for work with all
his
warehouses. This is what I have:
=SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21)

where E12:E21 is for the Material Type
B3 is material type that I want to get a total number on
A12:A21 is how many of that type

Now I can't figure out the other half of this. Most of the material type
has
a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need
to
add and then divide by 2 so it will give me an amount as if it were a 4x8.
But I need a calculation that will give me 1 number to represent a total
number of all. I hope this makes sense. The calculation as I have it, give
me
a total number but doesn't cut the 4x4 in half. Basically they can fit 2
of
the 4x4 in a 4x8 slot so they want to know how many 4x8's they have...

Please help and if its confusing, I can try again to explain it.
krista

Hi Biff, This is what I got from what you said, I must have done something
wrong because I keep getting an error. I can't use 4x8 because there is a
couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of
them... Can I use a wild card like ?x8 or like that?
=SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2))

"T. Valko" wrote:

Try something like this:

=SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2))

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
First I want to say thank you for all the help everyone is here. Now for
my
question. Still helping my husband do his spreadsheet for work with all
his
warehouses. This is what I have:
=SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21)

where E12:E21 is for the Material Type
B3 is material type that I want to get a total number on
A12:A21 is how many of that type

Now I can't figure out the other half of this. Most of the material type
has
a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need
to
add and then divide by 2 so it will give me an amount as if it were a 4x8.
But I need a calculation that will give me 1 number to represent a total
number of all. I hope this makes sense. The calculation as I have it, give
me
a total number but doesn't cut the 4x4 in half. Basically they can fit 2
of
the 4x4 in a 4x8 slot so they want to know how many 4x8's they have...

Please help and if its confusing, I can try again to explain it.
krista

Is this what your data looks like:

...........A..........B..........C
1......Type....Size........Qty
2........1........4x4..........2
3........1........4x8..........1
4........1........4x4..........4
5........2........4x8..........5

For Type 1, based on my understanding of your explanation the result should
be 4.

=SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2))

Result = 4

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
Hi Biff, This is what I got from what you said, I must have done something
wrong because I keep getting an error. I can't use 4x8 because there is a
couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all
of
them... Can I use a wild card like ?x8 or like that?
=SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2))

"T. Valko" wrote:

Try something like this:

=SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2))

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
First I want to say thank you for all the help everyone is here. Now
for
my
question. Still helping my husband do his spreadsheet for work with all
his
warehouses. This is what I have:
=SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21)

where E12:E21 is for the Material Type
B3 is material type that I want to get a total number on
A12:A21 is how many of that type

Now I can't figure out the other half of this. Most of the material
type
has
a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i
need
to
add and then divide by 2 so it will give me an amount as if it were a
4x8.
But I need a calculation that will give me 1 number to represent a
total
number of all. I hope this makes sense. The calculation as I have it,
give
me
a total number but doesn't cut the 4x4 in half. Basically they can fit
2
of
the 4x4 in a 4x8 slot so they want to know how many 4x8's they have...

Please help and if its confusing, I can try again to explain it.
krista

Hi Biff, Sorry for the confusion. Lets see if I can explain this better....
Each warehouse is set up as its own sheet. And its set up to look like this.
.......A...........B.......C....D......E.........F ......G............................etc
count hold wo qc type sku size
1 4 xps 4x4
2 23 iso 1x8
3 56 xps 4x8
4 4 xps 1x8
5 34 xps 4x8
6 23 xps 2x4

They have a quantity report that they have been manually figuring and
entering the totals of each type into, and my husbands boss wants him to have
it automatically do it for each of their warehouses. The report is set up
like this (I will only do it for 1 type instead of all the columns for all
the types)

.........A................B........C........D..... ......
2
3 warehouse XPS
4 Good Bad Hold
5 Beltman ? ? ?
6

They want to be able to look at this report and tell what each warehouse has
in it. So in the example, I would need how many good xps were at the beltman
warehouse. (A1:A6)..... But they want anything that is ?x4(4x4, 1x4 etc) to
be divided by 2 because they can fit 2 pallets in the 4x8 space...need to
also include in everything that is xps no matter what size......Hope this
makes sense.

"T. Valko" wrote:

Is this what your data looks like:

...........A..........B..........C
1......Type....Size........Qty
2........1........4x4..........2
3........1........4x8..........1
4........1........4x4..........4
5........2........4x8..........5

For Type 1, based on my understanding of your explanation the result should
be 4.

=SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2))

Result = 4

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
Hi Biff, This is what I got from what you said, I must have done something
wrong because I keep getting an error. I can't use 4x8 because there is a
couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all
of
them... Can I use a wild card like ?x8 or like that?
=SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2))

"T. Valko" wrote:

Try something like this:

=SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2))

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
First I want to say thank you for all the help everyone is here. Now
for
my
question. Still helping my husband do his spreadsheet for work with all
his
warehouses. This is what I have:
=SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21)

where E12:E21 is for the Material Type
B3 is material type that I want to get a total number on
A12:A21 is how many of that type

Now I can't figure out the other half of this. Most of the material
type
has
a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i
need
to
add and then divide by 2 so it will give me an amount as if it were a
4x8.
But I need a calculation that will give me 1 number to represent a
total
number of all. I hope this makes sense. The calculation as I have it,
give
me
a total number but doesn't cut the 4x4 in half. Basically they can fit
2
of
the 4x4 in a 4x8 slot so they want to know how many 4x8's they have...

Please help and if its confusing, I can try again to explain it.
krista

Ok, I think I understand now...

For Type xps...

Any size x8 = 1
Any size x4 = 1/2

=SUMPRODUCT(--(Type="xps"),((ISNUMBER(SEARCH("x8",Size)))*Count) +((ISNUMBER(SEARCH("x4",Size)))*Count/2))

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
Hi Biff, Sorry for the confusion. Lets see if I can explain this
better....
Each warehouse is set up as its own sheet. And its set up to look like
this.
......A...........B.......C....D......E.........F. .....G............................etc
count hold wo qc type sku size
1 4 xps 4x4
2 23 iso 1x8
3 56 xps 4x8
4 4 xps 1x8
5 34 xps 4x8
6 23 xps 2x4

They have a quantity report that they have been manually figuring and
entering the totals of each type into, and my husbands boss wants him to
have
it automatically do it for each of their warehouses. The report is set up
like this (I will only do it for 1 type instead of all the columns for all
the types)

........A................B........C........D...... .....
2
3 warehouse XPS
4 Good Bad Hold
5 Beltman ? ? ?
6

They want to be able to look at this report and tell what each warehouse
has
in it. So in the example, I would need how many good xps were at the
beltman
warehouse. (A1:A6)..... But they want anything that is ?x4(4x4, 1x4 etc)
to
be divided by 2 because they can fit 2 pallets in the 4x8 space...need to
also include in everything that is xps no matter what size......Hope this
makes sense.

"T. Valko" wrote:

Is this what your data looks like:

...........A..........B..........C
1......Type....Size........Qty
2........1........4x4..........2
3........1........4x8..........1
4........1........4x4..........4
5........2........4x8..........5

For Type 1, based on my understanding of your explanation the result
should
be 4.

=SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2))

Result = 4

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
Hi Biff, This is what I got from what you said, I must have done
something
wrong because I keep getting an error. I can't use 4x8 because there is
a
couple of other sizes mixed in as well like 1x8, 2x8 so I need to use
all
of
them... Can I use a wild card like ?x8 or like that?
=SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2))

"T. Valko" wrote:

Try something like this:

=SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2))

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
First I want to say thank you for all the help everyone is here. Now
for
my
question. Still helping my husband do his spreadsheet for work with
all
his
warehouses. This is what I have:
=SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21)

where E12:E21 is for the Material Type
B3 is material type that I want to get a total number on
A12:A21 is how many of that type

Now I can't figure out the other half of this. Most of the material
type
has
a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i
need
to
add and then divide by 2 so it will give me an amount as if it were
a
4x8.
But I need a calculation that will give me 1 number to represent a
total
number of all. I hope this makes sense. The calculation as I have
it,
give
me
a total number but doesn't cut the 4x4 in half. Basically they can
fit
2
of
the 4x4 in a 4x8 slot so they want to know how many 4x8's they
have...

Please help and if its confusing, I can try again to explain it.
krista

"kristap" wrote:
there is a couple of other sizes mixed in as well like 1x8, 2x8
so I need to use all of them.

Try the following array formula (commit with ctrl-shift-Enter, not just
Enter; Excel should enclose the formula with curly braces "{...}"):

=SUMPRODUCT(VLOOKUP($B$3, Table!$A$1:$E$5, MATCH(Beltman!E12:E21,
Table!$A$1:$E$1,0),0), Beltman!A12:A21)

This assumes that in A1:E5 in a worksheet named Table (of course, you can
customize all of that), you have a table of the following form:

1. A2:A5 contains the sizes of all pieces, e.g. 1x8, 2x8, 4x8 and 4x4.

2. B1:E3 also contains the same sizes of all pieces. I don't think the
order matters, but I would put them in the same order as #1.

3. Each cell in the table contains the number of each piece in column A that
can be made from the piece in row 1. For example, B2:E2 (1x8 piece) would
contain 1, 2, 4, 0.

The MATCH function determines the column to use in the table. The VLOOKUP
function determines the row to use in the table. The SUMPRODUCT multiplies
the number of pieces in stock (A12:A21) by the number of B3 pieces that can
made from them (E12:E21).

(Eventually, someone might be able to provide an equivalent non-array
formula.)


----- original message -----

"kristap" wrote in message
...
Hi Biff, This is what I got from what you said, I must have done something
wrong because I keep getting an error. I can't use 4x8 because there is a
couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all
of
them... Can I use a wild card like ?x8 or like that?
=SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2))

"T. Valko" wrote:

Try something like this:

=SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2))

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
First I want to say thank you for all the help everyone is here. Now
for
my
question. Still helping my husband do his spreadsheet for work with all
his
warehouses. This is what I have:
=SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21)

where E12:E21 is for the Material Type
B3 is material type that I want to get a total number on
A12:A21 is how many of that type

Now I can't figure out the other half of this. Most of the material
type
has
a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i
need
to
add and then divide by 2 so it will give me an amount as if it were a
4x8.
But I need a calculation that will give me 1 number to represent a
total
number of all. I hope this makes sense. The calculation as I have it,
give
me
a total number but doesn't cut the 4x4 in half. Basically they can fit
2
of
the 4x4 in a 4x8 slot so they want to know how many 4x8's they have...

Please help and if its confusing, I can try again to explain it.
krista