Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get a result from 2 variables
I have 2 sheets on the same file. The first one named UC COMPLETO is the
source where I have info from A1 to O5936. The columns important for me a A (Description), D (Bulk Size) and G (Qty received). This page is sorted by Description. The second one is a page I get from a department named Tuberias Int with info from A4 to J3553 where I have to compare that the qty requested is the same to the qty purchased. The department gives me this page with a column D named Description, C named Bulk Size and H named Volume but the rows are not the same from the original one because here we have not the requested but the purchased. What I need to know is if from page 2 for example cell H16 is the same quantity as the sum of those rows in the first page on column G but I have 2 variables: the bulk size and the description. I tried using this formula but there is a mistake and I'm not sure it would work: SUMIF(AND('UC COMPLETO'!A1594=D16,'UC COMPLETO'!D1594=C16),D16,'UC COMPLETO'!G1550:G1594) I know I have to open the range from A1 to A1594 and D1 to D1594 but I was doing just a test. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get a result from 2 variables
Just another sum product. Try this, but remember that the three ranges
specified in the formula must be the same size: =SUMPRODUCT(--('UC COMPLETO'!A4:A3553=D16),--('UC COMPLETO'!D4:D3553=C16),('UC COMPLETO'!G4:G3553)) Tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get a result from 2 variables
Try this in the first empty column on the Tuberias sheet, beginning in row 4:
=SUMPRODUCT(--('UC COMPLETO'!$A$1:$A$5936=D4),--('UC COMPLETO'!$D$1:$D$5936=C4),'UC COMPLETO'!$G$1:$G$5936)-H4 Copy down through row 3553. The formula returns the difference in the quantities between the sheets (zero if the quantities match). Hope this helps, Hutch "PaulinaDi" wrote: I have 2 sheets on the same file. The first one named UC COMPLETO is the source where I have info from A1 to O5936. The columns important for me a A (Description), D (Bulk Size) and G (Qty received). This page is sorted by Description. The second one is a page I get from a department named Tuberias Int with info from A4 to J3553 where I have to compare that the qty requested is the same to the qty purchased. The department gives me this page with a column D named Description, C named Bulk Size and H named Volume but the rows are not the same from the original one because here we have not the requested but the purchased. What I need to know is if from page 2 for example cell H16 is the same quantity as the sum of those rows in the first page on column G but I have 2 variables: the bulk size and the description. I tried using this formula but there is a mistake and I'm not sure it would work: SUMIF(AND('UC COMPLETO'!A1594=D16,'UC COMPLETO'!D1594=C16),D16,'UC COMPLETO'!G1550:G1594) I know I have to open the range from A1 to A1594 and D1 to D1594 but I was doing just a test. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get a result from 2 variables
Hi,
You could make your life a lot easier if you range named the three ranges on the first sheet A, D, and G Your formula would be =SUM(--(A=D4),--(D=C4),G)=H4 Which returns True if they match and False if they don't. Or you could have it read as tom's does =SUM(--(A=D4),--(D=C4),G)-H4 Notice with range names, as typically done, there is not sheet reference or absolute $ signs. You name a range by selecting it and typing the name into the Name Box on the far left of the Formula Bar and press ENTER. My names correspond to the Column letters, just to make things informative, and short. (You can't use C or R as range names) If this is helpful, please click the Yes button. -- Thanks, Shane Devenshire "PaulinaDi" wrote: I have 2 sheets on the same file. The first one named UC COMPLETO is the source where I have info from A1 to O5936. The columns important for me a A (Description), D (Bulk Size) and G (Qty received). This page is sorted by Description. The second one is a page I get from a department named Tuberias Int with info from A4 to J3553 where I have to compare that the qty requested is the same to the qty purchased. The department gives me this page with a column D named Description, C named Bulk Size and H named Volume but the rows are not the same from the original one because here we have not the requested but the purchased. What I need to know is if from page 2 for example cell H16 is the same quantity as the sum of those rows in the first page on column G but I have 2 variables: the bulk size and the description. I tried using this formula but there is a mistake and I'm not sure it would work: SUMIF(AND('UC COMPLETO'!A1594=D16,'UC COMPLETO'!D1594=C16),D16,'UC COMPLETO'!G1550:G1594) I know I have to open the range from A1 to A1594 and D1 to D1594 but I was doing just a test. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 LookUp Variables One Result | Excel Worksheet Functions | |||
two variables to get result | Excel Worksheet Functions | |||
TWO VARIABLES - ONE RESULT | Excel Discussion (Misc queries) | |||
showing lookup result with multiple variables | Excel Discussion (Misc queries) | |||
2 Conditions True, Many Variables, Return Result | Excel Worksheet Functions |