Sum with multiple conditions in Excel 2000
 

I have a sum problem described below. I have checked the help, a book
and this newsgroup for similar examples and tried to mimic the code,
but with no success so far. So if you know how to solve this, please
reply with an explicit formula, and not something like "try array
formulas".

A B C D
1 P1 P2 Val Fea
2 MO 5 F1
3 JL 2 F2
4 MO 12 F1
5 JL 1 F3
6 JL 4 F1
7
8 P1: MO 5
9 JL 6
10
11 9 F1
12 2 F2
13 0 F3

Row 1 contains column headers and rows 2-6 data. Rows 8-13 contains
various sums for when there are non-blank values in the P1 column.
Rows 14- (which I have omitted) contains similar sums related to when
the P2 column is populated.

C8 is a formula that sums up the values in the Val column where the
value in the P1 column equals that in B8. C9 i similar. This is no
problem, I use SUMIF (actually called SUMMA.OM in my swedish Excel
2000) which handles a single condition.

My problem is to write the formula in C11 (and C12 and C13). It should
sum up the values in the Val column where the value in the Fea column
matches D11, and the value in the P1 column is non-empty.
How do I do this?

Regards

Magnus Oskarsson

use this example to suit your needs where the range size is the same. NON
array.
=sumproduct((rngA="joe")*(rngB=d11)*rngc)

--
Don Guillett
SalesAid Software

"Magnus Oskarsson" wrote in message
om...
I have a sum problem described below. I have checked the help, a book
and this newsgroup for similar examples and tried to mimic the code,
but with no success so far. So if you know how to solve this, please
reply with an explicit formula, and not something like "try array
formulas".

A B C D
1 P1 P2 Val Fea
2 MO 5 F1
3 JL 2 F2
4 MO 12 F1
5 JL 1 F3
6 JL 4 F1
7
8 P1: MO 5
9 JL 6
10
11 9 F1
12 2 F2
13 0 F3

Row 1 contains column headers and rows 2-6 data. Rows 8-13 contains
various sums for when there are non-blank values in the P1 column.
Rows 14- (which I have omitted) contains similar sums related to when
the P2 column is populated.

C8 is a formula that sums up the values in the Val column where the
value in the P1 column equals that in B8. C9 i similar. This is no
problem, I use SUMIF (actually called SUMMA.OM in my swedish Excel
2000) which handles a single condition.

My problem is to write the formula in C11 (and C12 and C13). It should
sum up the values in the Val column where the value in the Fea column
matches D11, and the value in the P1 column is non-empty.
How do I do this?

Regards

Magnus Oskarsson

Hi
use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"Magnus Oskarsson" schrieb im Newsbeitrag
om...
I have a sum problem described below. I have checked the help, a book
and this newsgroup for similar examples and tried to mimic the code,
but with no success so far. So if you know how to solve this, please
reply with an explicit formula, and not something like "try array
formulas".

A B C D
1 P1 P2 Val Fea
2 MO 5 F1
3 JL 2 F2
4 MO 12 F1
5 JL 1 F3
6 JL 4 F1
7
8 P1: MO 5
9 JL 6
10
11 9 F1
12 2 F2
13 0 F3

Row 1 contains column headers and rows 2-6 data. Rows 8-13 contains
various sums for when there are non-blank values in the P1 column.
Rows 14- (which I have omitted) contains similar sums related to when
the P2 column is populated.

C8 is a formula that sums up the values in the Val column where the
value in the P1 column equals that in B8. C9 i similar. This is no
problem, I use SUMIF (actually called SUMMA.OM in my swedish Excel
2000) which handles a single condition.

My problem is to write the formula in C11 (and C12 and C13). It should
sum up the values in the Val column where the value in the Fea column
matches D11, and the value in the P1 column is non-empty.
How do I do this?

Regards

Magnus Oskarsson