Sum with multiple conditions in Excel 2000

[Magnus Oskarsson]

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

 

[Don Guillett]

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

 

[Frank Kabel]

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