sumproduct help

OK I was just searching through a bunch of the other warehouses and there ARE
a few, not many but a few that are NOT x8 or x4..... This is what I have come
up with and it worked so far for the 1 that i tried, will it work for them
all? The problem I have is the numbers are constantly changing, depending on
if they sell material in the warehouse or acquire more, so if they add
something that isn't x8 or x4 the numbers won't add up..... Will this work
for all or did I just get lucky?

=SUMPRODUCT(--(Beltman!E12:E21=B3),((RIGHT(Beltman!G12:G21,2)" x4")*Beltman!A12:A21)+((RIGHT(Beltman!G12:G21,2)=" x4")*Beltman!A12:A21/2))

"kristap" wrote:

Thank you so much. That worked... Sorry for all the confusion....

My husband is in bed right now, but I'm almost positive that the only sixes
are x4 and x8...... If by chance there are other sizes like x6 or whatever,
can I do something like:

=SUMPRODUCT(--(Type="xps"),((RIGHT(Size,2)="")*Count)+((RIGHT(Si ze,2)="x4")*Count/2))
or would I need to put someting in the quotes for it to be accurate?


"T. Valko" wrote:

We can shorten that a few keystrokes:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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
news 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

I just posted but I can't edit so I will answer this.... I am going to assume
that anything that isn't x4 will be as 1 because he didn't tell me otherwise.
And if its x6 or whatever all the others were, then there would be only 1 in
the 4x8 slot because I don't think we can double up, because it wouldn't fit.
I will clarify with him tomorrow though and may end up posting more questions
at that point... He's sleeping (its almost 1:00 AM here) so I will figure the
rest out tomorrow... Thank you so much for all your help. You are awesome....

"T. Valko" wrote:

If by chance there are other sizes like x6 or whatever,
can I do something like:
RIGHT(Size,2)=""

No. How should x6 be handled? As the formula is currently written x6 is
ignored. Should it be counted as 1?

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
Thank you so much. That worked... Sorry for all the confusion....

My husband is in bed right now, but I'm almost positive that the only
sixes
are x4 and x8...... If by chance there are other sizes like x6 or
whatever,
can I do something like:

=SUMPRODUCT(--(Type="xps"),((RIGHT(Size,2)="")*Count)+((RIGHT(Si ze,2)="x4")*Count/2))
or would I need to put someting in the quotes for it to be accurate?


"T. Valko" wrote:

We can shorten that a few keystrokes:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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
news 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

=SUMPRODUCT(--(Beltman!E12:E21=B3),((RIGHT(Beltman!G12:G21,2)" x4")*Beltman!A12:A21)+((RIGHT(Beltman!G12:G21,2)=" x4")*Beltman!A12:A21/2))

That'll probably work. What that means is:

Anything** that is not x4 = 1
x4 = 1/2

** anything means literally *anything* including empty cells


--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
OK I was just searching through a bunch of the other warehouses and there
ARE
a few, not many but a few that are NOT x8 or x4..... This is what I have
come
up with and it worked so far for the 1 that i tried, will it work for them
all? The problem I have is the numbers are constantly changing, depending
on
if they sell material in the warehouse or acquire more, so if they add
something that isn't x8 or x4 the numbers won't add up..... Will this work
for all or did I just get lucky?

=SUMPRODUCT(--(Beltman!E12:E21=B3),((RIGHT(Beltman!G12:G21,2)" x4")*Beltman!A12:A21)+((RIGHT(Beltman!G12:G21,2)=" x4")*Beltman!A12:A21/2))

"kristap" wrote:

Thank you so much. That worked... Sorry for all the confusion....

My husband is in bed right now, but I'm almost positive that the only
sixes
are x4 and x8...... If by chance there are other sizes like x6 or
whatever,
can I do something like:

=SUMPRODUCT(--(Type="xps"),((RIGHT(Size,2)="")*Count)+((RIGHT(Si ze,2)="x4")*Count/2))
or would I need to put someting in the quotes for it to be accurate?


"T. Valko" wrote:

We can shorten that a few keystrokes:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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
news 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