Sumproduct to exclude subtotal(9,...)

[Sagittarius1978]

Hello,

I have the following:

1:Overall 100 140%
2:Task1 20 100%
3:Task2 20 100%
4:Task3 20 100%
5:Tasks4-5 40 100%
6:Task4 20 100%
7:Task5 20 100%

And the formulas as follows:

1:Overall =SUBTOTAL(9,B2:B7) =IF(B1=0,0,SUMPRODUCT(B2:B32,C2:C32)/B1)
2:Task1 20 1
3:Task2 20 1
4:Task3 20 1
5:Tasks4-5 =SUBTOTAL(9,B6:B7) =IF(B5=0,0,SUMPRODUCT(B6:B7,C6:C7)/B5)
6:Task4 20 1
7:Task5 20 1

My problem is the subtotal in row 5 is not excluded from row:1 Col:c (should be 100% and not 140%)

Is there a way to exclude the subtotal from sumproduct just as subtotal excludes subtotal?

Kind Regards
Khaled

[Richard Buttrey]

Hello and welcome to the forum

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[dosydos]

use the aggregate formula

[Sagittarius1978]

Thanks, I couldn't upload it here, but I uploaded to Google drive, here is the link

https://drive.google.com/open?id=0B7...mhrcnR5ZEpXQ1k

[Sagittarius1978]

I am using office 2007, does it have this aggregate function?

[Tony Valko]

I am using office 2007, does it have this aggregate function?

No, the AGGREGATE function was introduced in Excel 2010.

[Sagittarius1978]

So any solution for 2007?

[Tony Valko]

Sorry, I don't understand what you're trying to do.

[Sagittarius1978]

Please check the sample file

https://drive.google.com/open?id=0B7...mhrcnR5ZEpXQ1k

[JeteMc]

I believe that all of the functions in the following formula are available in the 2007 version:

Please Login or Register  to view this content.

For future reference please attach .xlsx files directly by clicking on GO ADVANCED and then scrolling down to Manage Attachments to open the upload window (the paperclip icon does not work).
Let us know if you have any questions.

[Sagittarius1978]

Thank you so much for your reply, is there a more generic solution? I don't want to depend on the text of Column A, is there a way to check if there is a formula in the cell, then to exclude the cell?