sumproduct help ..

[KK1966]

Hi, morning everybody

Looking help the formula of Sumproduct ( not sure it's work or not) wanna base on the certain criteria to find the average result.

example

criteria at sheet1 B2
fine the range at sheet2 E2 to down if meet the criteria at sheet1 B2 than average the all value at B2 to down.

please help or if sumproduct are not suitable which please to advised.

[Opv]

Hi, morning everybody

Looking help the formula of Sumproduct ( not sure it's work or not) wanna base on the certain criteria to find the average result.

example

criteria at sheet1 B2
fine the range at sheet2 E2 to down if meet the criteria at sheet1 B2 than average the all value at B2 to down.

please help or if sumproduct are not suitable which please to advised.

I'm sure XLD or one of the other more experienced participants will address your question related to Sumproduct. Could you not achieve your objective by using SumIf divided by CountIf?

[KK1966]

I'm sure XLD or one of the other more experienced participants will address your question related to Sumproduct. Could you not achieve your objective by using SumIf divided by CountIf?

I FIND THE AVERAGEIF IS WORK .....
ANYWAY.. THXX SO MUHC

[Opv]

I FIND THE AVERAGEIF IS WORK .....
ANYWAY.. THXX SO MUHC

Now that you mention it, I am aware that there is an AverageIf function, but I'm still stuck in Excel 2000 so I don't have that function available. Out of sight; out of mind. Glad it's working for you.

[Aussiebear]

I FIND THE AVERAGEIF IS WORK .....
ANYWAY.. THXX SO MUHC

Please don't type in Capital letters.

[Bob Phillips]

Now that you mention it, I am aware that there is an AverageIf function, but I'm still stuck in Excel 2000 so I don't have that function available. Out of sight; out of mind. Glad it's working for you.

Even with Excel 2000, you can emulate functions like AVERAGEIF, MAXIF, MEDIANIF, etc., with array formulae, such as

=AVERAGE(IF(check_range=condition,number_range))

but I agree with you, for average would be best with SUMIF/COUNTIF.

But ... you really should upgrade your Excel, not to 2007 that is rubbish, but 2010 is worthy version. Not only will you get to experience the joys of the ribbon, Excel's own Marmite functionality, but there are lots of other improvements to warrant an upgrade.

[Opv]

Even with Excel 2000, you can emulate functions like AVERAGEIF, MAXIF, MEDIANIF, etc., with array formulae, such as

=AVERAGE(IF(check_range=condition,number_range))

but I agree with you, for average would be best with SUMIF/COUNTIF.

But ... you really should upgrade your Excel, not to 2007 that is rubbish, but 2010 is worthy version. Not only will you get to experience the joys of the ribbon, Excel's own Marmite functionality, but there are lots of other improvements to warrant an upgrade.

Thanks. I'll keep the upgrade in mind. I tend to find comfort in the old and familiar and resist change until I am forced into it. (I was among the last to give up DOS for Windows.) I created myself an AverageIf function last night after being reminded about the function.