If formula from 1 sheet to another

[adamheon]

I'm looking for a forumla that will multiply the number of colors by a specified length and organize it in another tab. Hopefully i can explain this properly.

In the attached spreadsheet under QUICKPULLS tab between rows 9 & 15 I have sample data entered. In columns H thru Z there are 3 rows of information (1,2,3). Row 1 should not have anything to do with this (just information). Row 2 has a drop down with 8 color selections (B,R,BL,W,BR,O,Y,G = BLACK, RED, BLUE, WHITE, BROWN, ORANGE, YELLOW, GRAY). Row 3 has a drop down with 5 size selections (14,12,10,8,6).

I have filled data with pretty much all options available like they could be entered.

The WIRETOTALS are as follows:

BLACK (B) SIZE 12 = 3
RED (R) SIZE 12 = 2
BLUE (BL) SIZE 12 = 3
WHITE (W) SIZE 12 = 4
BROWN (BR) SIZE 10 = 3
ORANGE (O) SIZE 10 = 3
YELLOW (Y) SIZE 10 = 2
GRAY (G) SIZE 10 = 4

RED (R) SIZE 6 = 2
WHITE (W) SIZE 6 = 2
YELLOW (Y) SIZE 8 = 1
BROWN (BR) SIZE 8 = 1
GRAY (G) SIZE 8 = 1

In cell D10 is the total length. Basically I need the total length (D10) multiplied by the WIRETOTALS and then sorted in the WIRETOTALS TAB.

In the WIRETOTALS tab, you will see a break down of colors and sizes. Notice that #8 and #6 only have 4 colors, thats because the 6 colors excluding white and gray should all total to black (BLACK, RED, BLUE, BROWN, ORANGE, YELLOW all should total to black) . I believe i need an IF formula in order to total the lengths of colors and sizes. I will write it out to try and explain better.

under WIRETOTALS tab cell M1, total the instances of RED size 12 between H10:Z11 & H14:Z15 and multiply it by cell D10....this would be typical for each cell but just changing the color and size as needed.

hope this makes sense, if anyone can point me in the right direction or provide a sample formula that would be awesome! thanks.

[ncmay]

To start, paste the following formula in cell L4 on the Wire Totals sheet

=SUMPRODUCT(('Quick Pulls'!H10:Z10="B")*('Quick Pulls'!H11:Z11=12))+SUMPRODUCT(('Quick Pulls'!H14:Z14="B")*('Quick Pulls'!H15:Z15=12))

Adjust the color letters, wire gage and test range in the remaining formulas

[adamheon]

This works perfect at giving me the totals. How would I then multiply the results by cell D10 on the QUICKPULLS tab?

[ncmay]

Here is a modified version to include the results being multiplied by the D10 number from the Quickpulls tab

=(SUMPRODUCT(('Quick Pulls'!H10:Z10="B")*('Quick Pulls'!H11:Z11=12))+SUMPRODUCT(('Quick Pulls'!H14:Z14="B")*('Quick Pulls'!H15:Z15=12)))*'Quick Pulls'!D10

[adamheon]

Thanks that worked great. I was missing the extra ).

[adamheon]

1 other question. Since i want to combine colors B, R, BL, BR, O, Y if they are size 8 or size 6 how would i do that. I tried this formula but there seems to be an error in it:

=(SUMPRODUCT(('Quick Pulls'!H10:Z10="B","R","BL","BR","O","Y")*('Quick Pulls'!H11:Z11=8))+SUMPRODUCT(('Quick Pulls'!H14:Z14="B","R","BL","BR","O","Y")*('Quick Pulls'!H15:Z15=8)))*'Quick Pulls'!D10

[ncmay]

Here is one way for the 8 gage wire you want to combine, may not be the best way

=(SUMPRODUCT((('Quick Pulls'!H10:Z10="B")+('Quick Pulls'!H10:Z10="R")+('Quick Pulls'!H10:Z10="BL")+('Quick Pulls'!H10:Z10="BR")+('Quick Pulls'!H10:Z10="O")+('Quick Pulls'!H10:Z10="Y"))*('Quick Pulls'!H11:Z11=8))+SUMPRODUCT((('Quick Pulls'!H14:Z14="B")+('Quick Pulls'!H14:Z14="R")+('Quick Pulls'!H14:Z14="BL")+('Quick Pulls'!H14:Z14="BR")+('Quick Pulls'!H14:Z14="O")+('Quick Pulls'!H14:Z14="Y"))*('Quick Pulls'!H15:Z15=8)))*'Quick Pulls'!D10

[adamheon]

It's good enough for me! thanks for your help!

[ncmay]

Here is a shorter version

=(SUMPRODUCT((('Quick Pulls'!H10:Z10={"B";"R";"BL";"BR";"O";"Y"}))*('Quick Pulls'!H11:Z11=8))+SUMPRODUCT((('Quick Pulls'!H14:Z14={"B";"R";"BL";"BR";"O";"Y"}))*('Quick Pulls'!H15:Z15=8)))*'Quick Pulls'!D10