Adding data in columns based on criteria in more than one column..

[Setts]

I asked this before but my explanation was labored and the answer didn't
address my needs. Here is a fuller, and I hope, clearer explanation. How do
add the figures in one or more columns based on criteria in more than one
column? For example assume the following worksheet:

A B C D E

1 aaa n n n
2 bbb zzz n n n
3 aaa yyy n n n
4 ccc xxx n n n
5 aaa www n n n
6 bbb n n n
7 bbb zzz n n n
8 ccc n n n

The real worksheet has hundreds of rows. I would like to add up the n's in
the rows that have a specific criteria, e.g. aaa in Column A and bull
(blanks) in Column B. I would also like to add up the n's in the rows that
have a specific criteria in Column A and anything in Column B (not null or
blank), e.g. aaa and yyy with the aaa and www. There are too many different
strings in Column B to use specific criteria. Any help would br greatly
appreciated. Setts

 

[Teethless mama]

=SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))

 

[Setts]

Would you kindly explain how this works? Since there are no commas it seems
you specified one argument and that argument is an expression not an array
reference. I have been trying various ways and get nothing that seems
usuable. Please explain how this adds up only those numbers that are in rows
that match data in other columns in those rows, including arguments that are
expressions (e.g. <>"") and others strings ("aaa"). Sorry for being so
dense. Setts

 

[T. Valko]

=SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))

That formula is counting the literal character "n". Is that what you wanted?

My interpretation of your post is "n" represents a number and you want the
SUM.

aaa in Column A and [n]ull (blanks) in Column B.
=SUMPRODUCT((A1:A8="aaa")*(B1:B8="")*C1:E8)

specific criteria in Column A and anything in Column B
aaa and yyy with the aaa and www.

=SUMPRODUCT((A1:A8="aaa")*(B1:B8<>"")*C1:E8)

 

[Setts]

Biff: Sorry, no go. I tried your SUMPRODUCT various ways and none worked.
Always ### Value ###. Is the suggested function have a reference to only
one array? There are no commas separating the arguments. Setts

=SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))

That formula is counting the literal character "n". Is that what you wanted?

My interpretation of your post is "n" represents a number and you want the
SUM.

aaa in Column A and [n]ull (blanks) in Column B.
=SUMPRODUCT((A1:A8="aaa")*(B1:B8="")*C1:E8)

specific criteria in Column A and anything in Column B
aaa and yyy with the aaa and www.

=SUMPRODUCT((A1:A8="aaa")*(B1:B8<>"")*C1:E8)

--
Biff
Microsoft Excel MVP

Would you kindly explain how this works? Since there are no commas it
seems
you specified one argument and that argument is an expression not an array
reference. I have been trying various ways and get nothing that seems
usuable. Please explain how this adds up only those numbers that are in
rows
that match data in other columns in those rows, including arguments that
are
expressions (e.g. <>"") and others strings ("aaa"). Sorry for being so
dense. Setts

 

[T. Valko]

OK, you haven't said which interpretation of your post is correct. So....

Here's a small sample file that demonstrates my interpretation. I used your
sample data and replaced "n" with random numbers.

Sample file:

xSumproduct.xls 14kb

http://cjoint.com/?gvihcq6juw

As you'll see the formula does work and it does return the correct results.

There are no commas separating the arguments.

Don't get "obsessed" over commas!

--
Biff
Microsoft Excel MVP

Biff: Sorry, no go. I tried your SUMPRODUCT various ways and none
worked.
Always ### Value ###. Is the suggested function have a reference to only
one array? There are no commas separating the arguments. Setts

=SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))

That formula is counting the literal character "n". Is that what you
wanted?

My interpretation of your post is "n" represents a number and you want
the
SUM.

aaa in Column A and [n]ull (blanks) in Column B.
=SUMPRODUCT((A1:A8="aaa")*(B1:B8="")*C1:E8)

specific criteria in Column A and anything in Column B
aaa and yyy with the aaa and www.

=SUMPRODUCT((A1:A8="aaa")*(B1:B8<>"")*C1:E8)

--
Biff
Microsoft Excel MVP

Would you kindly explain how this works? Since there are no commas it
seems
you specified one argument and that argument is an expression not an
array
reference. I have been trying various ways and get nothing that seems
usuable. Please explain how this adds up only those numbers that are
in
rows
that match data in other columns in those rows, including arguments
that
are
expressions (e.g. <>"") and others strings ("aaa"). Sorry for being so
dense. Setts

:

=SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))



:

I asked this before but my explanation was labored and the answer
didn't
address my needs. Here is a fuller, and I hope, clearer
explanation.
How do
add the figures in one or more columns based on criteria in more
than
one
column? For example assume the following worksheet:

A B C D E

1 aaa n n n
2 bbb zzz n n n
3 aaa yyy n n n
4 ccc xxx n n n
5 aaa www n n n
6 bbb n n n
7 bbb zzz n n n
8 ccc n n n

The real worksheet has hundreds of rows. I would like to add up the
n's in
the rows that have a specific criteria, e.g. aaa in Column A and
bull
(blanks) in Column B. I would also like to add up the n's in the
rows
that
have a specific criteria in Column A and anything in Column B (not
null
or
blank), e.g. aaa and yyy with the aaa and www. There are too many
different
strings in Column B to use specific criteria. Any help would br
greatly
appreciated. Setts

 

[T. Valko]

See this for a detailed explanation of how SUMPRODUCT can be used:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP

OK, you haven't said which interpretation of your post is correct. So....

Here's a small sample file that demonstrates my interpretation. I used
your sample data and replaced "n" with random numbers.

Sample file:

xSumproduct.xls 14kb

http://cjoint.com/?gvihcq6juw

As you'll see the formula does work and it does return the correct
results.

There are no commas separating the arguments.

Don't get "obsessed" over commas!

--
Biff
Microsoft Excel MVP

Biff: Sorry, no go. I tried your SUMPRODUCT various ways and none
worked.
Always ### Value ###. Is the suggested function have a reference to
only
one array? There are no commas separating the arguments. Setts

=SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))

That formula is counting the literal character "n". Is that what you
wanted?

My interpretation of your post is "n" represents a number and you want
the
SUM.

aaa in Column A and [n]ull (blanks) in Column B.

=SUMPRODUCT((A1:A8="aaa")*(B1:B8="")*C1:E8)

specific criteria in Column A and anything in Column B
aaa and yyy with the aaa and www.

=SUMPRODUCT((A1:A8="aaa")*(B1:B8<>"")*C1:E8)

--
Biff
Microsoft Excel MVP


Would you kindly explain how this works? Since there are no commas it
seems
you specified one argument and that argument is an expression not an
array
reference. I have been trying various ways and get nothing that seems
usuable. Please explain how this adds up only those numbers that are
in
rows
that match data in other columns in those rows, including arguments
that
are
expressions (e.g. <>"") and others strings ("aaa"). Sorry for being
so
dense. Setts

:

=SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))



:

I asked this before but my explanation was labored and the answer
didn't
address my needs. Here is a fuller, and I hope, clearer
explanation.
How do
add the figures in one or more columns based on criteria in more
than
one
column? For example assume the following worksheet:

A B C D E

1 aaa n n n
2 bbb zzz n n n
3 aaa yyy n n n
4 ccc xxx n n n
5 aaa www n n n
6 bbb n n n
7 bbb zzz n n n
8 ccc n n n

The real worksheet has hundreds of rows. I would like to add up
the
n's in
the rows that have a specific criteria, e.g. aaa in Column A and
bull
(blanks) in Column B. I would also like to add up the n's in the
rows
that
have a specific criteria in Column A and anything in Column B (not
null
or
blank), e.g. aaa and yyy with the aaa and www. There are too many
different
strings in Column B to use specific criteria. Any help would br
greatly
appreciated. Setts