sumproduct help

[kristap]

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

 

[T. Valko]

Try something like this:

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

 

[kristap]

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]

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

 

[JoeU2004]

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]

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]

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

 

[T. Valko]

We can shorten that a few keystrokes:

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

 

[kristap]

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(Size,2)="x4")*Count/2))
or would I need to put someting in the quotes for it to be accurate?

 

[T. Valko]

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?

 

[kristap]

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]

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]

=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