help with formula and multiple associated data sets

[JD]

I have a set of data that is linked to a certain Id number--in my table
called "PIN". In the base data set, there are associated #s, such as total
lot area, the zone, units, bldg sf etc however, each pin in this situation
has maybe 2 different total lot areas and portions that obviously exceed each
individual total lot area. Unfortunately, in the base table, I have also
created percentages of each portion of the particular total lot area, and
when adding the percentages, they exceed 100%. I want to look at the data of
the whole pin--including multiple lot areas. i have created a pivot table
that sums certain values "portion" and "percent lot" associated with such pin
and then through indirect pulls data from another table associated with these
sums, such as "percent lot". As seen below pin 3 has 2 different total lot
#'s with corresponding portions.

pin portion total lot percent lot
3 3 5 60%
3 2 5 40%
3 6 9 66%
3 3 9 33%

I have a more complete data set upon request...just don't know how to link
to it.

thanks.
How do I tabulate the total lot associated with pin 3 and have it come out
to 14 and not the sum of all the portions?

 

[JLatham]

In Excel 2003 and earlier you could use a SUMPRODUCT() formula that evaluates
both the pin and portion entries to give you a total of either of the other
two columns. I entered your labels at A1, B1, C1 and D1 with the values in
the rows below (A25). This formula gives the total of Pin=3 and portion=3
=SUMPRODUCT(--($A$2:$A$5=3),--($B$2:$B$5=3),($C$2:$C$5))
and if you enter any pin into E3 and any portion into F3 (just 2 cells I had
handy), the formula could be written as
=SUMPRODUCT(--($A$2:$A$5=E3),--($B$2:$B$5=F3),($C$2:$C$5))

If you Excel 2007 or 2010, you could use SUMIFS() to the same end.