multiple criterion to give sum

G

Guest

I need formula assistance with my summary worksheet. I want to take data
from my daily tracking log sheet of an item of two sizes (2's,3's(column E))
with six destination (Virginia, Kentucky, etc.(Column G - not shown in
eqation)) and give the cubic meters of the load (Column I) provided that both
criteria are met.

As one can see from the below eqation that it is easy to use a SUMIF
function to do this when the range is given. the problem arises when the
destination are intermitten and not continuous. I do not want to have a
string of SUMIF eqations.

=SUMIF('Daily Log'!E52:E55,2,'Daily Log'!I52:I55)+SUMIF('Daily
Log'!E57:E60,2,'Daily Log'!I57:I60)

I have tried to nest with an AND statement as well as IF. No luck with MATCH
and FIND. I either get a false or it only reads the first destination and
suns all of I regardless of destination.

I need assistance with an eqation the uses a name and a numeral in the same
row for the criteria of a numeral that needs to be summed by meeting both
criteria and populates that into one cell on a seperate sheet.

Any assistance would be greatly appreciated.

LT Hettinger
 
G

Guest

Here's one interp and proposal ..

Assume source data in sheet: Daily Log,
from row2 to an expected max row100 (say), where
Col G contains the 6 destinations, eg: Virgina, Kentucky, etc
Col E contains the sizes, eg: 2, 3, etc
Col I contains the load figs that's to be summed

In a new sheet,
List the 6 destinations within say: E1:E6 (Virgina, Kentucky, etc)

Then assuming the sizes (2,3, etc) are listed in A2 down,
we could place in B2:
=SUMPRODUCT((ISNUMBER(SEARCH($E$1:$E$6,'Daily Log'!$G$2:$G$100)))*('Daily
Log'!$E$2:$E$100=A2),'Daily Log'!$I$2:$I$100)
and copy B2 down

Col B will return the required totals from col I in Daily Log
for the corresponding sizes listed in col A found within col E in Daily Log,
where the 6 destinations are found within col G in Daily Log
 
G

Guest

Clarifications:
If you need it to be case sensitive (for the 6 destinations' comparison with
what's within col G in Daily Log), replace SEARCH with FIND in the formula in
B2. SEARCH is not case sensitive. Also note that entire col references cannot
be used in SUMPRODUCT. Adapt the ranges to suit ..

---
 
G

Guest

Sorry, just detected my typo error on "Virginia" in lines such as:
Col G contains the 6 destinations, eg: Virgina, Kentucky, etc
List the 6 destinations within say: E1:E6 (Virgina, Kentucky, etc)
Click to expand...

It's crucial to ensure that there's consistency between the 6 destination
names entered in E1:E6, and the destination names found within col G in Daily
Log.

---
 
Top