excel formulas to count and sum
 

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.

excel formulas to count and sum
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"mdoyle13" schrieb im Newsbeitrag
...
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="tex
t2",('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.

excel formulas to count and sum
 

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.

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"mdoyle13" schrieb im Newsbeitrag
...
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="tex
t2",('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.

excel formulas to count and sum
 

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.



"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"mdoyle13" schrieb im Newsbeitrag
...
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="tex
t2",('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.