Trying to get a result from 2 variables

[PaulinaDi]

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 are:
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.

 

[TomPl]

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'!D43553=C16),('UC COMPLETO'!G4:G3553))

Tom

 

[Tom Hutchins]

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

 

[ShaneDevenshire]

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.