excel formulas to count and sum

[mdoyle13]

I am looking for a way to count and sum results in a column based on data
from a range of rows from another worksheet.

The data in the sheet1 cells would be a number in a, text in b, text in c,
number in d and number in e. Ideally I'm looking for three formulas for
sheet2 cells b, c and d.

The first formula would count occurrences with these parameters: sheet1
cells a-e would all be a range, i.e. a1:a10, b1:b10 ... e1:e10. i need it to
count (not sum) the numbers in e1:e10 when all parts of the formula are true.
the formula i'm looking for would be similar to **
if(and('sheet1'!a1:a10=1,('sheet1'!b1:b10="text1",('sheet1'!c1:c10="text2",('sheet1"!d1:d10='sheet2'!a1))))

The second formula would be a sum of e1:e10 utilizing the same results from
the previous formula.

The third formula would give me the largest number from e1:e10, again with
the same results from the first formula.

Basically I want the number in e1 only if these conditions are met: a1=1,
b1="text1", c1="text2", d1='sheet2'!a1, same for row2, row3, etc.

I have tried count, countif, sumif, etc. but couldn't make them work in the
desired way.

Any tips on which direction to go will be appreciated greatly.

 

[Frank Kabel]

Hi
1. Try:
SUMPRODUCT(--('sheet1'!a1:a10=1),--('sheet1'!b1:b10="text1"),--('sheet1
'!c1:c10="text2"),--('sheet1"!d1:d10='sheet2'!a1))

2. Try:
SUMPRODUCT(--('sheet1'!a1:a10=1),--('sheet1'!b1:b10="text1"),--('sheet1
'!c1:c10="text2"),--('sheet1"!d1:d10='sheet2'!a1),'sheet1'!e1:e10)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[mdoyle13]

That sent me in the right direction. I should be able to dramatically cut the
size of my file by not needing as many formulas to get the same result.
Thanks for the input.

 

[mdoyle13]

Sumproduct has worked wonderfully. With a few tweaks it has even worked on
some other data in the file instead of large sections of individual formulas,
thus greatly reducing the file size.

The one thing I haven't been able to isolate is the largest number in the
e1:e10 range when the row sumproduct formulas for a,b,c,d and e are all true.
I have tried MAX and LARGE but always the result returned is the largest in
that range, i.e. if the row of true statements is 4 (a4,b4...e4) and e4 is
20, but e10 is 24, the result it is giving me is 24 instead of 20.

Thanks in advance.