• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum based on multiple criteria

T

Thomas Kuriakose

Active Member
Respected Sirs,

We have a workbook with products and category and the different parameter of costs incurred for each of these.

We need the sum of the values based on the specific product (G1:AP1) and specific category (A2:A42) to be listed on the summary as mentioned in the workbook.

The constraint is the format cannot be changed

Kindly find attached the file for your reference.

with regards,
thomas
 

Attachments

Respected Sirs,

Thank you for the support on this.

The product headers in row G1:API will vary based on each requirement, The total number of products are 37, and for any requirement we will not have all the 37 products with the parameter costs.

For example we can have 10 products for one requirement, so we need to lookup up the category for these listed products and then sum the parameters for the 10 products in the respective category summary.

The value in H10 has been input in this example.

Thank you very much,

with regards,
thomas
 

Attachments

Hi,

Another option in keep the OP's original layout without adding helper column and row.

In I10, formula copy down :

=SUM(OFFSET(F$1,MOD(ROW(A1)-1,6)+1,MATCH(LOOKUP(2,1/(LOOKUP(1,-FIND(G10,F$9:F10),F$9:F10)=A$2:A$42&" "&B$2:B$42),C$2:C$42),$G$1:$AQ$1,0),,SUMPRODUCT(--(LOOKUP(ROW($2:$42),ROW($2:$42)/(A$2:A$42>0),A$2:A$42)&" "&B$2:B$42=LOOKUP(1,-FIND(G10,F$9:F10),F$9:F10)))))

Regards
Bosco
 

Attachments

Respected Sir,

Amazing, you are the formula champion.

Thank you so much for the non-helper solution provided.

very much appreciated,

with regards,
thomas
 
You must log in or register to reply here.
Back
Top