Help with an array formula?

[potta]

Hi, I have a set of data as follows:

A (Year) B (PerformanceIndicator) C(Number)
2007 PI 1 10
2007 PI 1 10
2008 PI 2 15
2007 PI 2 15
2007 PI 2 15

I would appreciate help with a formula that:

sums column c (number), if column a (year)=2007 and if colum b b(Performance Indicator)=PI1

I have been using formulas such as:

{=SUM(IF($D$1:$D$49999=$G$2,IF($CG$1:$CG$49999=$G$3,1,0),0))}

to count a total number of rows that meet certain criteria, however i now need to sum the total of a third column that meet two criteria from two other columns.

for example, i would be looking for an answer of 20 from the data above where the year is 2007 and the PI is PI 1.

Many Thanks

[NBVC]

Just replace the 1 in the formula with the range to sum, e.g. $C$1:$C$49999

Please Login or Register  to view this content.

you can also use sumproduct() which doesn't need to be specially confirmed...

Please Login or Register  to view this content.

[potta]

thankyou. i'm guessing that with 'sumproduct' the list of criteria can be extended endlessly?

so to add a third colum to that formula:

*(CH1:CH49999) after the first two criteria and before the column to sum?

[NBVC]

thankyou. i'm guessing that with 'sumproduct' the list of criteria can be extended endlessly?

so to add a third colum to that formula:

*(CH1:CH49999) after the first two criteria and before the column to sum?

Yup... you'd probably add a condition, though... like CH1:CH49999=???

[potta]

Cheers... for